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.
Hello all!
I have what I believe to be a straight forward problem but really struggling with the solution.
I want to get the number of active (open) franchises in a region and create a new column with that information. I can do this is Python relatively easy but I'm trying to do as much in PowerBI/DAX as I can. So for example, a sample table (Table1) is below:
Region | Franchise # | Open |
New England | 111 | Yes |
Arkoma | 211 | Yes |
Magnolia | 311 | No |
Magnolia | 312 | Yes |
New England | 112 | No |
Arkoma | 212 | Yes |
Arkoma | 213 | No |
Arkoma | 214 | Yes |
New England | 113 | No |
Magnolia | 313 | Yes |
I have another table that has as many rows as their are regions - in this case lets assume I have the three regions listed. So what I want to do is filter based on the unique regions and then count the number of entries in that filtered region that are == "Yes". (Get the number of active franchises in each region).
ActiveFranchisesPerRegion = CALCULATE(COUNTA('Table1'[Open]), FILTER('Table1', DISTINCT('Table1'[Region]) ))
However I'm getting the error: 'A table of multiple values was supplied where a single value was expected'. I'm not surprised to get the error as I know the code and filters are not right.
My expected result would be:
Region | ActiveFranchisesPerRegion |
New England | 1 |
Arkoma | 3 |
Magnolia | 2 |
Can anyone help to set up my filters/code in the right way to help address this problem?
fdasfasf
Solved! Go to Solution.
Hi @Anonymous
1. Place Table1[Region] in the rows of a table visual
2. CREATE this measure and place it in the visual
Measure = CALCULATE( DISTINCTCOUNT( Table1[Franchise #] ), Table1[Open] = "Yes")
Please mark the question solved when we get to the solution and consider kudoing if posts are helpful.
Cheers
Hi @Anonymous
1. Place Table1[Region] in the rows of a table visual
2. CREATE this measure and place it in the visual
Measure = CALCULATE( DISTINCTCOUNT( Table1[Franchise #] ), Table1[Open] = "Yes")
Please mark the question solved when we get to the solution and consider kudoing if posts are helpful.
Cheers
Perfect. I knew it was simple, just needed som help.
Thank you!
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 |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |