Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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: ...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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].

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi 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]? 

AllisonKennedy's avatar
AllisonKennedy
Icon for Community Champion rankCommunity Champion
4 years ago

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