How to count multiple distinct values?

Question

I have written a DAX which is supposed to mark a 1 in the cell if the Organisation has been with multiple (distinct) sectors, however, I keep running into some issues.

Dup = 
CALCULATE( 
    COUNT('Inspection Scheduling Spreadsheet'[Licensed Establishment / Organisation]),
    FILTER(
        VALUES ('Inspection Scheduling Spreadsheet'[Licensed Establishment / Organisation] ),
        DISTINCTCOUNT( 'Inspection Scheduling Spreadsheet'[Sector]) > 1 
    )
)

The column Dup should be returning 0 because the organisation has been with the same sector.

Whereas, this is when Dup should return a 1 So whenever an organisation has been with more than one sector the column Dup should return a 1 else 0

mkRabbani · Accepted Answer · 2020-09-23 11:36:30Z

1

Try this below code-

Dup =

VAR current_row_org = MIN('Inspection Scheduling Spreadsheet'[Licensed Establishment / Organisation])

VAR dist_count =  
CALCULATE( 
    DISTINCTCOUNT('Inspection Scheduling Spreadsheet'[Sector]),
    FILTER(
        ALL('Inspection Scheduling Spreadsheet'),
        'Inspection Scheduling Spreadsheet'[Licensed Establishment / Organisation] = current_row_org
    )
)

RETURN IF(dist_count > 1,1,0)

answered Sep 23, 2020 at 11:36

mkRabbani

16.9k2 gold badges17 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

jcoke Over a year ago

I tested the above code and everything just returns 0

mkRabbani Over a year ago

This should work. Did you changed any part in the code? If so, please share the new Measure code here.

jcoke Over a year ago

I just created a custom column and placed the entire code inside and it returns 0 for every organisation

mkRabbani Over a year ago

In case of custom column, just create the first variable as - VAR current_row_org = 'Inspection Scheduling Spreadsheet'[Licensed Establishment / Organisation]

Collectives™ on Stack Overflow

How to count multiple distinct values?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related