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,
I'll try to explain my problem with example.
This is my data:
I want to create visualization that will show the total number of 'As', 'Bs', 'Cs' and 'Ds' in my four columns. So the result should be:
A - 5
B - 4
C - 2
D - 1
What I did was: I choosed Clustered Bar Chart > Insert Column 1 in Values > New Quick Measure > Addition > Added Colum 2 > New Quick Measure > Addition > Added Column 3 > New Quick Measure > Addition > Added Column 4.
What I got is this:
As you can see, this is not the result I wanted. I got 6 Bs, 4 As, 2 Cs, and no Ds, which doesn't correspond to the real data.
What should I do to get the correct result?
I'd really appreciate your help.
Thank you!
Solved! Go to Solution.
You might use Power Query to reshape your table and then create a measure using DAX to show the count of occurences. Hope this solution helps.
1. Click on Edit Queries -> select Transform tab -> select all your columns and then click on unpivot columns
2. once done, you will get the following table
3. Click on Close and apply. Now use the following DAX to create a measure as below:
You might use Power Query to reshape your table and then create a measure using DAX to show the count of occurences. Hope this solution helps.
1. Click on Edit Queries -> select Transform tab -> select all your columns and then click on unpivot columns
2. once done, you will get the following table
3. Click on Close and apply. Now use the following DAX to create a measure as below:
Thank you everyone!
This method with unpivoting columns works if I only had these 4 columns. However, I have additional columns, and when I unpivot these columns, that disrupts my statistics for other variables.
Just a thought. You can create a new table using those 4 columns, unpivot it and get the desired result. For rest of the calculations you can use your original table. Let me know if this works.
Hi, you can go to Edit Query, Select your columns and Apply an Unpivot Columns. Finally delete empty rows
Now with this, just apply a simple count in your visual
Regards
Victor
HI @Nejovic
How about creating measures like -->
COUNTA = COUNTROWS(FILTER(Table1,Table1[1] = "A")) + COUNTROWS(FILTER(Table1,Table1[2] = "A")) + COUNTROWS(FILTER(Table1,Table1[3] = "A")) + COUNTROWS(FILTER(Table1,Table1[4] = "A"))
And then showing a visual for all measures?
Is that applicable?
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 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |