Forum Discussion
Count from multiple columns
Hi All,
I have two sheets Sparepart dataset and unique sparepart list
My data set
Case id,
Status,
Sparepart1
Sparepart2
Sparepart3
Sparepart4
Sparepart5
Sparepart6
Sparepart7
Sparepart8
Depending on the repair case multiple spareparts can be used
So if only 1 spartpart was used entry will be only in sparepart1 and if 2 spareparts were used entry will be in Sparepart1 & Sparepart2
I have tried to create a measure
Spares:=VAR P1=Calculate(Countrows(dataset sheet),Sparepart1<>Blank())
P2=Calculate(Countrows(dataset sheet),Sparepart2<>Blank())
P3=Calculate(Countrows(dataset sheet),Sparepart3<>Blank())
P4=Calculate(Countrows(dataset sheet),Sparepart4<>Blank())
P5=Calculate(Countrows(dataset sheet),Sparepart5<>Blank())
P6=Calculate(Countrows(dataset sheet),Sparepart6<>Blank())
P7=Calculate(Countrows(dataset sheet),Sparepart7<>Blank())
P8=Calculate(Countrows(dataset sheet),Sparepart8<>Blank())
RETURN
P1+P2+P3+P4+P5+P6+P7+P8
This is is duplicating the counts
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
10 Replies
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 PessoaHi 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
that doesn't appear to be valid syntax for tables & columns. I am using it like below and that seems to work fine..
spares = VAR P1=Calculate(Countrows('dataset sheet'),'dataset sheet'[Sparepart1]<>Blank()) etc.Appologies for the confusion...
Want i want to achieve with the measure for sparepart count is creating a pivot table with unique spartpart in rows and months in columns so that I can use slicers for Brand/Product/Region etc to get more insights
When i use the my measure or the one suggested I am getting incorrect counts...
