Count from multiple columns

[Avicric]

Hi All,

I need help to create a measure which would provide the count of spareparts used
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

[jpessoa8]

Hi @Avicric ,

I'm sorry but I'm failing to understand the outcome you need.

Base on thedata on the 2 prints you've sent, can you do another one with the expected resulted?

[Avicric]

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...

[jpessoa8]

 @Avicric 

The print you've provided doesn't include any date or month information...

How is that included in your model?

[Avicric]

 @jpessoa8 heres and updated picture

For months i have added an column using format("mmm-yy") for created and closed month

Dates are in dd-mm-yy format

[jpessoa8 · Accepted solution]

@Avicric 

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

[sjoerdvn]

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.

[jpessoa8]

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

[Avicric]

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

[jpessoa8]

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

[Avicric]

@jpessoa8 

The measure is not duplicating the count of spareparts used. It is however duplicating the count of the number of cases in which a sparepart is used.

Any suggestions to fix this...

Also i am not using power bi 

I am working on power pivot to achieve this..