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
Community 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
 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi AllisonKennedy (& bcdobbs ),

     

    Thanks again for your replies. I'm sure you provided a working solution, it just doesn't seem to do the trick in my 'messy' report.. I'm unable to share my .pbi file here (because it contains sensitive data), but I'll try to explain what I'm dealing with.

     

    This report is about 'availability' of certain objects.

     

    • I have a table with the start- and end-date/times of objects' unavailability.
    • I have a table with the start- and end-date/times of objects' availability.
    • I have a table with the start- and end-date/times of object's reasons to be unavailable.
    • I have a table with minimum availability per date/time that should be met for specific groups of objects.
    • Of course, I also have tables with object codes and the groups they belong to, as well as a calendar table. The calendar date/time-fields are per whole hour. So the 'mm:ss'-part is always 00:00.

     

    Using this data, I have created measures that:

    • count the number of objects that were available at a certain 'whole' hour (BLUE)
    • count the number of objects that were unavailable at a certain 'whole' hour, but categorised into:
      • no reason for unavailability (RED)
      • with reason for unavailability (YELLOW)

    In the screenshot below, you can see what this looks like, the thin green line is the minimum availability:

     

     

    My task now was to create a card-visual with the unique number of days that the red part of the graph crosses the green line (at least once per counted day). In the example above, this should be '1'.

     

    Flags = IF([Number of objects available & unavailable with valid reason] - [Minimum number of objects] < 0, MIN(Calender[DateTime]) & ": FLAG", BLANK())

     

    When I create a table with the DateTime from the calendar (used in the graph above) and this new measure 'Flags', I get:

     

     

    Well that's great, I thought: almost there! I just need to count how many distinct values are in 'Flags'!

     

    Now when I apply your solution I get:

     

     

    I have no idea where the '3' comes from. In other cases with lots of flags, your measure gives me '23' instead of '16', or '28' instead of '18'.

     

    By the way, each of these visuals was filtered on a certain month through the calendar-table, as well as a filter on a group of objects through a objects table.

     

    Any idea what I'm doing wrong?

     

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

      Hi Anonymous ,

      As there's blank value in the Flags measure, so there's also blank row in the _TableIndex, so the formula count all the rows.

      I modify the formula like this:

       

      Count =
VAR _TableContext =
    ADDCOLUMNS (
        'Calender',
        "Row Context", Calender[Date],
        "Complex Measure", [Flags]
    )
VAR _TableIndex =
    SUMMARIZE ( _TableContext, [Complex Measure] )
VAR _Result =
    COUNTX (
        FILTER ( _TableIndex, [Complex Measure] <> BLANK () ),
        [Complex Measure]
    )
RETURN
    _Result

       

      Get the correct result.

      I attach my sample below for reference.

       

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

    • AllisonKennedy's avatar
      AllisonKennedy
      Community Champion
      4 years ago

      Anonymous  What is the exact measure you're using so we can analyze what you're doing wrong? 

       

      Since you have time as well, need to ensure you're providing the correct row context for the measure.