CODEWITHSUNDEEP
powerbidax
jcoke
jcoke

Reputation: 1891

How to count multiple distinct values?

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 enter image description here So whenever an organisation has been with more than one sector the column Dup should return a 1 else 0

Upvotes: 1

Views: 106

Answers (1)

mkRabbani
mkRabbani

Reputation: 16908

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)

Upvotes: 1

Related Questions