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.
Hey Everyone, I know this is going to be simple for someone experienced in PBI. I am still getting use to specific functions and could use some help. Here is a sample dataset:
Product ID | Color | Count |
123 | Red | 1 |
124 | Blue | 1 |
125 | Blue | 1 |
126 | Red | 1 |
127 | Yellow | 1 |
128 | Red | 1 |
129 | Yellow | 1 |
130 | Red | 1 |
131 | Blue | 1 |
Then I am counting by each color group (My dax is currently doing this fine):
Color | Count |
Red | 4 |
Blue | 3 |
Yellow | 2 |
I need help getting this output:
Product ID | Group Count |
123 | 4 |
124 | 3 |
125 | 3 |
126 | 4 |
127 | 2 |
128 | 4 |
129 | 2 |
130 | 4 |
131 | 3 |
I believe this Calculated Column should get you what you need:
ColorCount = CALCULATE( COUNTA([Color]), ALLEXCEPT( 'Product', 'Product'[Color] ))
Product IDColorCountColorCount
123 | Red | 1 | 4 |
124 | Blue | 1 | 3 |
125 | Blue | 1 | 3 |
126 | Red | 1 | 4 |
127 | Yellow | 1 | 2 |
128 | Red | 1 | 4 |
129 | Yellow | 1 | 2 |
130 | Red | 1 | 4 |
131 | Blue | 1 | 3 |
Regards,
I need to show the ProductID without the color
Once you have your Calculated Column in the data model, you can create any Visual that you want.
For a simple Table Visual, simply use your ProductID and ColorCount columns. You don't need to show the Color field in your visual.
This is a better sample dataset... I only added two rows, but shows the problem I am facing with the dax formula you shared above.
Product ID | Color | Count |
123 | Red | 1 |
124 | Blue | 1 |
125 | Blue | 1 |
126 | Red | 1 |
127 | Yellow | 1 |
128 | Red | 1 |
129 | Yellow | 1 |
130 | Red | 1 |
131 | Blue | 1 |
123 | Red | 1 |
124 | Blue | 1 |
Are you now saying your Table includes duplicates?
Product ID | Color | Count |
123 | Red | 1 |
124 | Blue | 1 |
123 | Red | 1 |
124 | Blue | 1 |
Do you want these duplicates counted or not?
Good question. And sorry for not explaining better. I still need it to return this:
Product ID | Group Count |
123 | 4 |
124 | 3 |
125 | 3 |
126 | 4 |
127 | 2 |
128 | 4 |
129 | 2 |
130 | 4 |
131 | 3 |
Which means it would be a distinct count of Product ID, but showing the subtotal count for each color.
So, I think you are looking for a new Calculated Table.
Unfortunately, I have to sign off, but will try to look at it later this evening or tomorrow for you.
Kind Regards,
Do not want to leave you hanging if this issue is still unresolved. Would like to try to clean this up if need be. I think the first step is to get this calculated column from my original response working for you.
ColorCount = CALCULATE( COUNTA([Color]), ALLEXCEPT( 'Product', 'Product'[Color] ))
If possible, please attach a sample pbix file and I can try to work with that.
Regards,
Nevermind... I don't have the option to attach files. See this link: https://drive.google.com/file/d/1kDUwCJccvUTnm-czvExzVi9t8YUXWyo4/view?usp=sharing
Think I got it straightened out now. My original reply was to create a Calculated Column. You inadvertently created a Measure. That is why it was not working the way I intended.
Please review the attached with my corrections. I hope this now resolves the issue.
Best Regards,
I see. Unfortunately this doesn't work for my actual data... it works for this scenario, but my dataset is much more granular and this formula is counting every row in the table.
I am still trying to figure it out. I attached a pbix file to the orignial post. Thank you for your help.
Dang... my actual dataset is much more complex and I am not able to do that... the ProductID and Color was too simple.
See example.
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 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |