How to create a distinct count measure that considers a MAX condition in another column

[Anonymous]

Hi Everyone,

Still learning and improving my Power BI and DAX skills, I'm stucked today while doing a report with the following problem:

What I need is to distinct count the values in a column (OPP IDS), but when there is a value that is duplicate, I need to check a condition in another column (REVENUE) and see which of the two/three/etc values is the biggest.

Example Table:

OPP NAME, OPP IDS, REVENUE

A, 1, 10000
B, 2, 15000
C, 2, 40000
D, 3, 20000
E, 4, 35000
F, 4, 8000

G, 4, 10000

In this example, my measure should allow me to show in the visualizations 4 values, A, C, D y E, since when there was a duplicate or more, those had the highest revenue.

Hope it's clear and i'm following the guidelines of the forum when requesting help. Let me know if I didn't. I tried to use the following post to solve my problem, but I couldn't work around the MAX condition:

https://community.powerbi.com/t5/Desktop/Create-Measure-for-Distinct-Count-of-a-column-based-on-crit...

Regards,

[v-yulgu-msft · Accepted solution]

Hi @Anonymous ,

Please refer to below measures:

Max REVENUE per ID =
IF (
    SUM ( table2[REVENUE] )
        = CALCULATE ( MAX ( table2[REVENUE] ), ALLEXCEPT ( table2, table2[OPP IDS] ) ),
    SUM ( table2[REVENUE] ),
    BLANK ()
)

distinct count =
CALCULATE (
    DISTINCTCOUNT ( table2[OPP NAME] ),
    FILTER ( table2, [Max REVENUE per ID] <> BLANK () )
)

Best regards,

Yuliana Gu

[AlB]

Hi @Anonymous

I'm a bit confused regarding hat you need, since you talk first about a distinct count and then about showing the names in the visualizations. I've gone with the latter.

You can create a measure like the following. It can probably be done in a simpler manner but well, it should work. Then place the measure in the visual level filters and select Show items when the value is --> 1

Table2 is the table you show. The measure needs to have Table2[OPP NAME] in the rows of the visual for it to work.    

ShowMeasure = 
VAR _MaxList =
    CALCULATETABLE (
        CALCULATETABLE (
            DISTINCT ( Table2[OPP NAME] );
            FILTER (
                Table2;
                Table2[REVENUE]
                    = CALCULATE ( MAX ( Table2[REVENUE] ); ALLEXCEPT ( Table2; Table2[OPP IDS] ) )
            )
        );
        ALL ( Table2 )
    )
RETURN
    IF ( SELECTEDVALUE ( Table2[OPP NAME] ) IN _MaxList; 1 )

[Anonymous]

Hi!

Thanks for your reply. I shouldn't have mentioned the part of the names, since It does not matter for what I'm trying to achive here.

The bottom line is that I need to do a Distinct Count of the OPPS IDS, and in the event that an OPP ID is duplicated, the value that needs to prevail for the count is the one with the highest revenue. If I don't do this, then when I use this data in my visualization and crossreference it with other categories, I don't get the unique values but I also get the duplicate one.

I've tried using your formula and replacing all the values with my data but It did not work. I think there is an error in the syntax maybe. Thanks though.

[AlB]

@Anonymous

There's no syntax error. You can see it working in this file. Maybe you need to change the ";" for "," depending on you OS locale settings.

Regarding the distinctcount, I'm still confused. If you simply want a distinct count of OPS IDD, just use DISTINCTCOUNT('Table'[OPS IDD]) and that's it, right? I don't get the part about the MAX revenue. It's completely irrelevant for the distinctcount. The distinctcount of OPS IDD will be the same regardless of the Revenue column. 

Now if what you mean is that you want to keep only the rows that have the MAX revenue for each OPPS ID, you can create a calculated table like the following and then use it for whatever you need. Also included in the file. Table2 is your initial table as shown.

CalculatedTable =
FILTER (
    Table2;
    Table2[REVENUE]
        = CALCULATE ( MAX ( Table2[REVENUE] ); ALLEXCEPT ( Table2; Table2[OPP IDS] ) )
)