Forum Discussion
Distinct count on measure within table context
Hi all,
Frustration is at an all-time high today..
I have a very complex model in my PowerBI. Lots of tables and measures.
In the end, I have created this table using a complex measure:
| Calendar[DateTime] | Calendar[Complex Measure] |
| 1-1-2022 16:00 | 1-1-2022: FLAG |
| 1-1-2022 17:00 | 1-1-2022: FLAG |
| 1-1-2022 18:00 | 1-1-2022: FLAG |
| 2-1-2022 9:00 | 2-1-2022: FLAG |
| 2-1-2022 12:00 | 2-1-2022: FLAG |
I want to count the distinct values in Dimension[Complex Measure] and present the result in a card-visual. In this case this should be '2'.
The table above only looks like this when I include both [DateTime] & [Complex Measure].
[Complex Measure] contains other measures from other tables that depend on the Calendar[DateTime]-context. It basically looks like:
Complex Measure = IF(Table1[Measure X] - Table2[Measure Y] < 0, MIN(Calender[DateTime]) & ": FLAG", BLANK())
How do I create a new measure that performs a correct distinct count on Calendar[Complex Measure] so I can use that in a card-visual?
Okay, I somehow managed to create a working measure.
It looks like this:CALCULATE( COUNTROWS(SUMMARIZE(Calender, Calender[Date])) + 0, FILTER(Calender, [Number of objects available & unavailable with valid reason] - [Minimal number of objects] < 0) )
I completely skipped the part where I create a measure that contains the word 'FLAG' and then performing a (distinct) count on that measure. I think the problem was that in my 'FLAG'-measure, I used:MIN(Calender[DateTime])
.. which is why it only worked when I included the [DateTime].
12 Replies
I agree with AllisonKennedy the code provided should be doing what you need.
You could try this similar pattern:
https://www.sqlbi.com/blog/marco/2018/05/31/how-to-write-distinctcountx-in-dax/
Anonymous See if this works for you:
Distinct Count Complex Measure =VAR _TableContext = ADDCOLUMNS( Calendar, "Row Context", Calendar[DateTime], "Complex Measure", [Complex Measure])
VAR _TableIndex = SUMMARIZE(_TableContext, [Complex Measure] )
VAR _Result = COUNTROWS( _TableIndex)RETURN _ResultHi AllisonKennedy,
Thanks for your response.
It seems that your measure is not giving me the desired result. My best guess is that you're not taking 'distinct' values but that you're counting all rows that have a result for [Complex Measure]?Anonymous Can you provide screenshots of what you've done and it not working? The SUMMARIZE function should group it by the value of the complex measure column we created in the first variable. I've renamed the virtual column I created to make it a little less ambiguous:
Distinct Count Complex Measure =VAR _TableContext = ADDCOLUMNS( 'Calendar', "Row Context", 'Calendar'[DateTime], "Complex Measure In Context", [Complex Measure])
VAR _TableIndex = SUMMARIZE(_TableContext, [Complex Measure In Context] )
VAR _Result = COUNTROWS( _TableIndex)RETURN _Result
