Count of Total Customers by Tasks

[ronaldbalza2023]

Hi everyone, I was puzzle how to count the total number of customers by Tasks by the given below scenario. Thanks in advance

Two tables have the inactive relationships: Tasks[Jobs Job Client ID] and Clients[ID]

DAX Measure

Total Customers by Tasks = 
CALCULATE (
    DISTINCTCOUNT ( Clients[ID] ),
    FILTER ( Clients, Clients[Is Prospect] = "No" ),
    USERELATIONSHIP ( Clients[ID], Tasks[Jobs Job Client ID] )
)

[TomMartens · Accepted solution]

Hey @ronaldbalza2023 ,

I do not understand your question because I do not see the problem.

Nevertheless, I would write the measure like this

Total Customers by Tasks = 
CALCULATE (
    DISTINCTCOUNT ( Clients[ID] ),
    Clients[Is Prospect] = "No",
    USERELATIONSHIP ( Clients[ID], Tasks[Jobs Job Client ID] ),
    CROSSFILTER( Clients[ID], Tasks[Jobs Job Client ID], BOTH )
)

Referencing only the column [Is Prospect] as a filter is more efficient than using the function FILTER (reading the complete table, all the columns), as the vertipaq engine does not need to read the whole table.

See CROSSFILTER here: CROSSFILTER – DAX Guide
CROSSFLTER is necessary as you want the column on the many-side of the relationship filtering the table of the one-side (at least this is my understanding of the problem you are trying to solve.

Regards,

Tom

[ronaldbalza2023]

Hi @TomMartens, thanks for taking the time on this. It is the crossfilter function that I am looking for. As you can see on the snapshot table, it only aggregates the data and is not segregated the total number of customers by tasks. Thanks and appreciated it 🙂

[TomMartens]

Hey @ronaldbalza2023 ,

please let me know if the CROSSFILTER function provides what you are looking for.

Regards,

Tom

[ronaldbalza2023]

It is @TomMartens ...I am looking for a ways to optimize the dax 🙂 Thanks again for your help.