Forum Discussion
Count from multiple columns
Adding the date columns to dataset following the Unpivot approach, the next step is to had a Date table and relate it to the existing tables via DateStart
With that, you can drap a month column in a matrix with the spareparts and add the measure for counting:
Here is the PowerBI version with this implementation: https://we.tl/t-dpKfsMa7sx
Hope this answer solves your problem!
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudo 👍
Thanks!
You can also check out my LinkedIn!
Best regards,
Jorge Pessoa
Hi Avicric ,
If I understood correctly your dataset is something like this right?
Do you really need to have the data in this format to do that calculation?
You could, in PowerQuery, unpivot the SpareParts columns, and with this you will have one row per CaseID and SparePart and it will be a simple COUNTROWS() of the table:
Here is the PowerBI I used for this example : https://we.tl/t-MmD4LGlIau
Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudo 👍
Thanks!
You can also check out my LinkedIn!
Best regards,
Jorge Pessoa
Hi jpessoa8
No thats not how my dataset looks,
Image1 is the dataset
Image2 is the unique part list(this is just an example my actual list is a total of 800 unique spareparts)
I want to show count of sparepart month wise/region/etc
sjoerdvn when i use the created measure the values are duplicating for example in the image for my dataset hammer was used 3 but for some reason the count for hammer is incorrect
Hope this helps you to help me
Hi Avicric ,
What I was missing in my approach was specifying the type of SparePart.
With that information, the only difference is to relate unpivoted table I've suggested with the SpareParts list table:
With this you can see the information either by CaseID or SparePart item:
Here is the revised PowerBI file : https://we.tl/t-CSZCMgmdYh
For additional help, please @ me in your reply!
You can also check out my LinkedIn!
Best regards,
Jorge Pessoa