Distinct Count of Column per another column - respecting slicers

[StidifordN]

I have a requirement to include a disctinct count per another column, but both columns need to exist in the table.

The scenario is - i have multiple suppliers servicing 1 or multiple client accounts - i want to get a distinct count of Client Accounts serviced per supplier - but I require both the Supplier and Client account in the table, whilst having the table still respect any slicers selected on the report page.

Sample output table below -

Output 1 being the distinct count of Accounts that exist per Supplier
Output 2 will be a simple if Output 1 > 1 then Yes, otherwise No.

Account	Supplier	Output 1	Output 2
Account 1	Supplier ABC	3	Yes
Account 1	Supplier DEF	1	No
Account 1	Supplier GHI	1	No
Account 2	Supplier JKL	1	No
Account 3	Supplier ABC	3	Yes
Account 3	Supplier MNO	2	Yes
Account 4	Supplier ABC	3	Yes
Account 4	Supplier MNO	2	Yes

I have attempted measures with distinctcount and allexcept(supplier) - but this then ignores any page filters applied, and for this report they are critical.

[Bibiano_Geraldo]

Hi @StidifordN ,

Create one measure for output 1:

Output 1 = 
CALCULATE(
  DISTINCTCOUNT( YourTable[Account] ),
  ALLSELECTED( YourTable[Account] )
)

Now create a measure for output 2 :

Output 2 = 
IF( [Output 1] > 1, "Yes", "No" )

Drag the field for the table visual, and your finaly output will look like this:

[v-kpoloju-msft]

Hi @StidifordN, 
Thank you for reaching out to the Microsoft fabric community forum. Thank you @OwenAuger, for your inputs on this issue.

After thoroughly reviewing the details you provided, I was able to reproduce the scenario, and it worked on my end. I have used it as sample data on my end and successfully implemented it.    

Dax Measure for Output 1 in table:

Output 1 =

CALCULATE(

    DISTINCTCOUNT('Table'[Account]),

    REMOVEFILTERS('Table'[Account])

)

Dax Measure for Output 2 in table:

Output 2 =

IF([Output 1] > 1, "Yes", "No")

Outcome:

I am also including .pbix file for your better understanding, please have a look into it:

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[OwenAuger · Accepted solution]

Hi @StidifordN,

I would suggest:

Output 1 = 
CALCULATE (
    DISTINCTCOUNT ( YourTable[Account] ),
    ALLSELECTED ( ),
    VALUES ( YourTable[Supplier] )
)

• ALLSELECTED ( ) restores the outer or "overall" filter context of the visual.
• VALUES ( YourTable[Supplier] ) retains the visible Supplier on a given "row" of the visual as a filter.
• The intersection of these should give the result you want.

Does this work for you?

[StidifordN]

THIS is the solution - many thanks.  Worked like a charm.