Distinct count on measure within table context

[zudar]

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?

[bcdobbs]

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/

[AllisonKennedy]

@zudar  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 _Result

[zudar]

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]

@zudar 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

[zudar]

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?

[AllisonKennedy]

@zudar  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.

[v-yanjiang-msft]

Hi @zudar ,

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.

[zudar]

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]

Hi @zudar ,

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.

[zudar]

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

[zudar]

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

[zudar · Accepted solution]

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