Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
143 | |
80 | |
65 | |
52 | |
49 |
User | Count |
---|---|
212 | |
89 | |
79 | |
68 | |
60 |