Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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,

1 ACCEPTED SOLUTION
v-yulgu-msft
Employee
Employee

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

1.PNG

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-yulgu-msft
Employee
Employee

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

1.PNG

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
AlB
Super User
Super User

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
Not applicable

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.

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

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.