Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
Check it out now!Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
I am trying to create a Measure within a table called 'All Sales'. The Measure should calculate the ratio of times "1" appears in the [Sales] column in the 'Sales Numbers' table, to the number of records (ie rows) in the 'All Sales' table. I dont want to copy the Sales column into the All Sales table as to keep the data files small - therefore using Measure and not adding a column. The issue is when using Countrows or If statments it won't let me reference any column outside the 'All Sales' table.
The formula I thought (along with may other versions reads like:
RecordID | Sales |
1 | 1 |
2 | 3 |
3 | 5 |
4 | 1 |
'All Sales' Table
RecordID | Sold |
1 | 7 |
2 | 4 |
3 | 9 |
4 | 3 |
5 | 8 |
So the resulting Measure should = 2/5 = 2.5
Thanks for the help as I know there has to be an easy way
Solved! Go to Solution.
Hi,
Create a single column table called Records with only unique Record ID in that table. Create a relationship (Mnay to One and Single) from the RecordID column of the 2 data tables to the RecordID column of the new table. Write these measures and drag the last one to a card visual
Measure1 = calculate(COUNTROWS('sales'),'Sales'['Sales Numbers'], == "1")
Measure2 = COUNTROWS('All Sales')
measure3 = divide([Measure1],[Measure2])
Hope this helps.
Hi,
Create a single column table called Records with only unique Record ID in that table. Create a relationship (Mnay to One and Single) from the RecordID column of the 2 data tables to the RecordID column of the new table. Write these measures and drag the last one to a card visual
Measure1 = calculate(COUNTROWS('sales'),'Sales'['Sales Numbers'], == "1")
Measure2 = COUNTROWS('All Sales')
measure3 = divide([Measure1],[Measure2])
Hope this helps.
Read about CALCULATE()
OK I think with the above suggestions and correcting the syntax I've worked it out.
As Ahsish Mathur says you can create two seperate Measures, then divide those. This makes it easier to follow. Originally I wanted to avoid this to minimise processing time of 3 measures when loading in millions of row data situations, and creating more items in the Fields section.
So combining this into one equation reads:
Use DAX Studio to examine the query plans.