Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Avicric
Frequent Visitor

Count from multiple columns

 

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

jpessoa8_0-1701369154216.png

 

With that, you can drap a month column in a matrix with the spareparts and add the measure for counting:

 

jpessoa8_1-1701369242818.png

 

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

View solution in original post

10 REPLIES 10
jpessoa8
Super User
Super User

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?

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

 @Avicric 

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

How is that included in your model?

IMG20231130213912.jpg

@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

@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

jpessoa8_0-1701369154216.png

 

With that, you can drap a month column in a matrix with the spareparts and add the measure for counting:

 

jpessoa8_1-1701369242818.png

 

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
Super User
Super User

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
Super User
Super User

Hi @Avicric ,

If I understood correctly your dataset is something like this right?

jpessoa8_0-1701193416388.png

 

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:

jpessoa8_1-1701193514122.png

 

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

IMG_20231129_094433.jpg

IMG_20231129_094405.jpg

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:

 

jpessoa8_0-1701245593755.png

 

With this you can see the information either by CaseID or SparePart item:

 

jpessoa8_1-1701245873667.png

 

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

 

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

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors