Using ALLSELECTED and DISTINCTCOUNT

[Anonymous]

Hello Power BI Community!

I was wondering if anyone can help with a DAX.

I have a table with different clients and personel (from different locations) and I want to obtain an ALLSELECTED total but only counting DISTINCT clients. The table looks like this:

USER |   CLIENT NAME           | LOCATION
   1          RONALD W                    A
   2          RONALD W                    A
   2          RONALD W                    A
   3          RONALD W                    A
   3          RONALD W                    A
   6          STACY L                          A
   7          JHON C                          B
   8          GENERIC CLIENT1          B
   9          GENERIC CLIENT2          B
  10         GENERIC CLIENT3          C

Basically, the result Im looking for is the total of distinct clients (6) per segment and this total to repeat for every user per location. So I can calculate the number of unique clientes attended by every personel divided by the total amount of distinct clientes that
have been attended.

I tried something like this but I haven't been able to integrate the distinctcount function (is it even possible?) to this ALLSELECTED:

CALCULATE(COUNTROWS(ALLSELECTED(Table1;ClientNamesColum));table1[segment] IN {"Segment A"})

However, this formula only gives me the total amount of clientes attended, not the TOTAL DISTINCT AMOUNT of Clientes. Appreciate the help in advance.

OGB.

[v-yangliu-msft · Accepted solution]

Hi  @Anonymous ,

Calculate the number of unique clientes attended by every personel divided by the total amount of distinct clientes that
have been attended.

Here are the steps you can follow：

1. Create measure.

measure1 =
CALCULATE(DISTINCTCOUNT('Table'[Client Name]),FILTER(ALLSELECTED('Table'),'Table'[Location]=MAX('Table'[Location])))

measure1 =
CALCULATE(DISTINCTCOUNT('Table'[Client Name]),FILTER(ALLSELECTED('Table'),'Table'[Location]=MAX('Table'[Location])))

divide =
DIVIDE([measure1],[measure2])

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[amitchandak]

@Anonymous , Try like

CALCULATE(DistinctCOUNT(Table[CLIENT NAME]), filter(ALLSELECTED(Table1),table[segment] IN {"Segment A"}))