Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi!
I cannot seem to figure out how to create a measure that would calculate the number of times a value appears in another column.
My tables and the measure result I would like:
Column1 - Column 2 - Measure (total 3)
1 4 0
2 5 0
3 5 0
4 1
5 2
6 0
7
Seems simple, but how to solve this in a measure?
Thanks in advance for any thoughts!
Solved! Go to Solution.
Hi @PBIacc721
Unfortunately, I don't know what is the structure of your dataset, but it worked ok when applied to the sample you have provided.
All() - Removes all filters from a current filter context this allows the measure to count occurrences of 5 that are in different rows.
VAR x = VALUES( 'Table'[Column1] ) -- this part selects distinct value for column1 in each give row and all values for total RETURN CALCULATE( COUNTROWS( 'Table' ), -- count rows in a table in a filter context created by CALCULATE ALL(), - removes all filters TREATAS( x, 'Table'[Column2] ) -- filters table where column2 = column1 ) + 0
Hi @PBIacc721
You can try the below.
Measure = VAR x = VALUES( 'Table'[Column1] ) RETURN CALCULATE( COUNTROWS( 'Table' ), ALL(), TREATAS( x, 'Table'[Column2] ) ) + 0
Thanx!
It works well on the row level, however the measure total does not give the sum on the row values. What is the logic for the measure total calculation?
Secondly, could you please tell me, why the ALL() is needed in there (why without it it does nnot work on the row level?
Thanks in advance!
Hi @PBIacc721
Unfortunately, I don't know what is the structure of your dataset, but it worked ok when applied to the sample you have provided.
All() - Removes all filters from a current filter context this allows the measure to count occurrences of 5 that are in different rows.
VAR x = VALUES( 'Table'[Column1] ) -- this part selects distinct value for column1 in each give row and all values for total RETURN CALCULATE( COUNTROWS( 'Table' ), -- count rows in a table in a filter context created by CALCULATE ALL(), - removes all filters TREATAS( x, 'Table'[Column2] ) -- filters table where column2 = column1 ) + 0
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |