Using Power BI

[siufai6]

always always remember power BI works with relational table structure (2/13/2024)

If you have data in the form of a complex header (e.g. city, and then product category as column headers),

Power BI cannot use this form of data straight away. you need to convert it into a relational table. like below:

To do this.. you transpose the original data so city and category becomes columns (fields in a table). And then unpivot the year data so that it becomes a de-normalized table ... so columns becomes city, category, year, value.

[siufai6]

Adding Constant lines to Scatter chart in power BI

In visualization pane, click "add further analysis to your visual". x-axis and y-axis. click on fx to add average of PL and average of Duration. Then we have the constant lines in scatter chart.

[siufai6]

Date dimension

table is a popular pattern in power BI. Basically it's a date dimension (image it as a decode table) that contains ALL dates ! To create... in table view.. add column then there is a DAX formula to create. To allow to create charts like seasonality chart, you can create additional columns like month only (or week only). Then you link it to your FACT table (data table). When creating a viz, drag the date you want (e.g. seasonality chart i just drag the month).. then add e.g. PL (sum, average..etc). Then you created a seasonality chart.

You can have many date columns in one table link to this date dimension table (imagine : stock purchase date, stock sell date).

When you create a visualization.. which one is it using?? the purchase date or the sell date?? it's the "active" relationship one.. I dunno.. may be it's not very useful if you need multiple charts one with purchase date and one with sell date??

[siufai6]

Summarizing data on power BI.

I have a table named trades with these cols: code and pl. I would like to summarize average gain and average loss.

1. Add a new measure of average of pl
2. Add another measure ("avg loss") that average when pl is <0 like below CALCULATE ( [avg PL], KEEPFILTERS ( FILTER ( ALL (trades[PL] ), trades[PL] <0 ) ) )
3. do the same for pl >=0 name it "avg gain"
4. create a table viisualization and add code, "avg gain", "avg loss" to the table.
5. Then you have a table with code and the respective average gain/loss.

[siufai6]

Summarize (and why to avoid it)

• I am able to understand only a part of it
• basically it's saying Power BI put data into clusters when calculating summary. A implicit filter is used when doing calculation.
• This implicit filter actually includes all other fields in the table (implicit). So if you need want to remove some filter, it actually keeps other "implicit " filters so you get results not expected.
• read this again some time later: https://www.sqlbi.com/articles/all-the-secrets-of-summarize/

[siufai6]

DAX calculated columns and Power Query computed columns (M expression)

• calculated columns are columns we add in the table view when we create a new columns that "stores" the results based on calculations.
• computed columns are columns we add in power query (when we define the data loading logic)

[siufai6]

Show tooltip that contains a visual

1. create a new page, under format, select page type as tooltip
2. choose the field that should show when hover over (e.g. the original chart has sales amount bar, then we choose sales amount)
3. create a visual on the page e.g. a sales trend chart.
4. Go back to original page, in the visual, choose Format, and select Properties -> under tooltips and select the page we created in step 3.

[siufai6]

Terms

• measure - summarize data using data analysis expression (DAX)