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.
I want to create two calculated columns in my data. I want the first one to show the number of sites for each hospital system and the second to show the total number clients in that system. Here is a simple example below. In Excel I would use Sumif to create the last two columns but how do I do it using DAX for calculated columns. I need these columns to create additional columns and measures later on. Please help.
Health System | Sites | Count of Sites | Customer Status | Count of customers | Total Number of Sites in Health System | Total Number of Customers in Health System |
UMASS | site 1 | 1 | YES | 1 | 8 | 5 |
UMASS | site 2 | 1 | NO | 0 | 8 | 5 |
UMASS | site 3 | 1 | YES | 1 | 8 | 5 |
UMASS | site 4 | 1 | YES | 1 | 8 | 5 |
UMASS | site 5 | 1 | NO | 0 | 8 | 5 |
UMASS | site 6 | 1 | YES | 1 | 8 | 5 |
UMASS | site 7 | 1 | YES | 1 | 8 | 5 |
UMASS | site 8 | 1 | NO | 0 | 8 | 5 |
NYU | site A | 1 | YES | 1 | 4 | 3 |
NYU | site B | 1 | YES | 1 | 4 | 3 |
NYU | site C | 1 | NO | 0 | 4 | 3 |
NYU | site D | 1 | YES | 1 | 4 | 3 |
Solved! Go to Solution.
Tot Num Customer = CALCULATE(COUNT(Health[Customer Status]),ALLEXCEPT(Health,Health[Health System]),Health[Customer Status] = "YES") Tot Num of Sites = CALCULATE(Count(Health[Health System]),ALLEXCEPT(Health,Health[Health System]))
Hi @IgorGershenson ,
I did these as measures, so you can drop them on the visual. They match what you have posted. Thanks for the clarity in your posting! The CALCULATE allows this measure to iterate through the whole table, and the ALLEXCEPT keeps the one variable the same.
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Proud to be a Super User!
Total Number of Sites in Health System = CALCULATE ( DISTINCTCOUNT ( Table1[ Sites] ), ALLEXCEPT ( Table1, Table1[Health System] ) )
Total Number of Customers in Health System = CALCULATE ( SUM ( Table1[ Count of Customers] ), ALLEXCEPT ( Table1, Table1[Health System] ) )
Please mark the question solved when done and consider giving kudos if posts are helpful.
Cheers
Total Number of Sites in Health System = CALCULATE ( DISTINCTCOUNT ( Table1[ Sites] ), ALLEXCEPT ( Table1, Table1[Health System] ) )
Total Number of Customers in Health System = CALCULATE ( SUM ( Table1[ Count of Customers] ), ALLEXCEPT ( Table1, Table1[Health System] ) )
Please mark the question solved when done and consider giving kudos if posts are helpful.
Cheers
Tot Num Customer = CALCULATE(COUNT(Health[Customer Status]),ALLEXCEPT(Health,Health[Health System]),Health[Customer Status] = "YES") Tot Num of Sites = CALCULATE(Count(Health[Health System]),ALLEXCEPT(Health,Health[Health System]))
Hi @IgorGershenson ,
I did these as measures, so you can drop them on the visual. They match what you have posted. Thanks for the clarity in your posting! The CALCULATE allows this measure to iterate through the whole table, and the ALLEXCEPT keeps the one variable the same.
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Proud to be a Super User!
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 |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
19 |