Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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] )
)
Solved! Go to 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
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
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 🙂
Hey @ronaldbalza2023 ,
please let me know if the CROSSFILTER function provides what you are looking for.
Regards,
Tom
It is @TomMartens ...I am looking for a ways to optimize the dax 🙂 Thanks again for your help.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |