March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hi All,
Solved! Go to Solution.
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 ,
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...
The print you've provided doesn't include any date or month information...
How is that included in your model?
@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
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
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.
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
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..
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
26 | |
21 | |
20 | |
13 | |
12 |
User | Count |
---|---|
40 | |
27 | |
27 | |
21 | |
19 |