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 v-yanjiang-msft, looks like your suggestion alsmost did the trick..

 

I copy-pasted your formula and the same table now shows:

 

 

Any idea why this is happening?

v-yanjiang-msft's avatar
v-yanjiang-msft
Community Support
4 years ago

Hi Anonymous ,

As you said, you want to present the result in a card-visual, it will get the correct result 2 in a card visual.

As for why each row returns count 1, it need to analyze the formula of measures [Number of objects availabe & unavailable with valid reason] and [Minimum number of objects] in the measure Flags.

 

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    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 v-yanjiang-msft,

     

    As you can see in the table, the card-visual should only return one distinct value from 'Flags': 1/19/2022: FLAG. The correct result should be '1' and not '2'.

     

    The measures behind [Number of objects availabe & unavailable with valid reason] and [Minimum number of objects] look like:

     

    [Number of objects availabe & unavailable with valid reason] is a sum of two measures that have this form:

    SUMX ( 
    VALUES ( Table ),
    IF (
       ...,
        VAR X = 
            CALCULATE ( [Measure], ... in {"...","..."} ) 
        VAR Y = 
            CALCULATE ( [Measure], ... in {"...","..."} ) 
        RETURN
             SWITCH(
                 TRUE(), 
                 X > 0 && Y > 0, 0.6,
                 X > 0 && Y = 0, 1,
                 X = 0 && Y > 0, 0,
                 0
                 )
            )
        )

     and:

    CALCULATE(COUNTROWS(Table),
FILTER(Table,
Start<=MAX(Calender[DateTime])
&& (End>=MIN(Calender[DateTime]) || End = BLANK())
))

     

    [Minimum number of objects] basically looks like:

    SUM(...) - IF(SUM(...) = BLANK(), BLANK(), [Measure])
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    v-yanjiang-msft (& AllisonKennedy )

    I'm revisiting this topic two and a half years later. I haven't been able to figure it out and would appreciate your help on this one. The measure I'm looking for is included in a monthly report and right now, I'm doing the embarrassing monthly task of counting distinct values for 'flag' by hand and writing the correct result (for 10+ groups) into a excel file which I then use in the PowerBI. Does my previous reply give enough information to conclude anything?