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 there!
I have a question about a certain type of distinct count in DAX.
I have a table that lists different customers, their contract number, and the number of locations each customer has. The customer may have different contracts but the total number of locations that is recorded on each contract remains the same. For example, Customer A has three different contracts but the total number of locations the customer has remains the same, in this case 1 location.
I now want to get a total number of locations for this table. The result in the example below should be 5, as customer A has 1 location, Customer B has 3 locations, and Customer C has 1 location. However, if I just sum it up, the result is 18, since it counts everything. Could somebody tell me what measure would be best to get the result I am after? I hope this all makes sense. Thanks a lot!
Solved! Go to Solution.
Hey @Anonymous ,
with DAX you have to take one step more.
First you should create a virtual table with the customers and the number of locations. Then you can summarize by company.
The following measure is doing that and should solve your problem:
Number of Locations V2 =
-- Create a virtual table of the distinct Customer Values and add the maximum number of locations per Customer
VAR vSummarizedTable =
ADDCOLUMNS(
VALUES( myTable[Customer] ),
"@NumberOfLocations",
CALCULATE(
MAX( myTable[Number of Locations] )
)
)
RETURN
-- Summarize the number of locations of the table from above
SUMX(
vSummarizedTable,
[@NumberOfLocations]
)
Hey @Anonymous ,
with DAX you have to take one step more.
First you should create a virtual table with the customers and the number of locations. Then you can summarize by company.
The following measure is doing that and should solve your problem:
Number of Locations V2 =
-- Create a virtual table of the distinct Customer Values and add the maximum number of locations per Customer
VAR vSummarizedTable =
ADDCOLUMNS(
VALUES( myTable[Customer] ),
"@NumberOfLocations",
CALCULATE(
MAX( myTable[Number of Locations] )
)
)
RETURN
-- Summarize the number of locations of the table from above
SUMX(
vSummarizedTable,
[@NumberOfLocations]
)
Hey @Anonymous ,
did it work with the Measure I provided?
I'm curious if you could solve this issue 🙂
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 |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |