Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Power Bi Team Please Help!
I'm a data intern making a report for my orgnization and stumbled upon a puzzle I can not resolve. When searching forums or through google for a resolution, I can not find my answer as I can't word it the way someone might have before me.
A little back story:
We are a manufacturing firm who use qr codes and scanners to collect information (time and cost) per drawing. Each drawing has a unique part ID.
We are now releasing drawings by scanning them into the system first and allocating a stage and status. Release "R" (stage) and Complete "C" (Status). Over time the part will hit Production "P" (stage) and go through and be Completed "C". We also have these stage and status for, inventory and assembly as well.
I want to have count that anything that's in manufacturing complete had to have been first released. Meaning that the Unique part ID has to match and be counted as one.
Example Part 123 Release Complete, Part 123 Manufacturing complete, Count 1.
part 123 Release Complete, part 123 manufacturing start, Count blank.
The DAX formula I currently am using is:
Manufacturing Complete = CALCULATE (
DISTINCTCOUNT('pmiscan PartStage'[partId]),
FILTER ( ALLSELECTED ( 'pmiscan PartStage' ), 'pmiscan PartStage'[partStageStage] = "R" ),
FILTER(ALLSELECTED('pmiscan PartStage' ), 'pmiscan PartStage'[partStageStatus] = "C")
FILTER ( ALLSELECTED ( 'pmiscan PartStage' ), 'pmiscan PartStage'[partStageStage] = "P" ),
FILTER ( ALLSELECTED ( 'pmiscan PartStage' ), 'pmiscan PartStage'[partStageStage] = "C" ))
Which is not working, as when I do this in edit qurieres it works well but I need to do it in DAX.
Please help I'm stuck
Thank you
So to help us out, what are you trying to do in simple terms? It seems that each item has a Stage and a Status.
It looks like you want to a count of Items that have a Stage greater than "Release" and a Status of "Complete". Is this correct? Can you share a full list of statuses and stages that are available and tell us the ranking order of them?
Without seeing your data structure, it looks like you want to do something like this:
Manufacturing Complete =
CALCULATE ( DISTINCTCOUNT('pmiscan PartStage'[partId]), FILTER (
ALLSELECTED ( 'pmiscan PartStage' ),
'pmiscan PartStage'[partStageStage] IN {"R", "P", "S"} && 'pmiscan PartStage'[partStageStatus] = "C") )
)
Whenever a new part is scanned to the system it prepopulates 5 entries for the different stages.
(Stage) Status
Release (ST/SP/C)
Production (ST/SP/C)
Assembly (ST/SP/C)
Inventory (ST/SP/C)
Rework (ST/SP/C)
The status will only be these three options (ST/SP/C). When a user scans it, the program will automatically change the status to match.
When Release = c for part ID 123
and when Production = c for part ID 123
I want to count this as 1.
Release is the first step in our manufacturing process, I want to know that when a part that was released has been finished with manufacturing that I can count this as one. The reason why we are doing this is to understand compleation of a project through this.
Please see attatched photos I was able to do this in edit quieres but I need to have it as a Dax.
Thank you everyone for helping me out,
Ah, this makes a bit more sense. I didn't realize how part stage id was used
So to keep things simple, let's bullet point out the requirements for this DAX measure:
So with these points in mind, we can create a measure that counts the "interesting" part ids. I'll bring you step by step through my process, so not all of the measures will even be valid.
First we should filter the table down to just distinct combinations of Part ID, partStageStage, and partStageStatus.
Step1 = SUMMARIZE('PartStage', 'PartStage'[partId], 'PartStage'[Stage], 'PartStage'[Status])
From here filter out the irrelevant rows. This is both rows with an uninteresting Stage, and rows with a bad status.
Step2 = FILTER(SUMMARIZE('PartStage', 'PartStage'[partId], 'PartStage'[Stage], 'PartStage'[Status]), ('PartStage'[Stage] = "R" || 'PartStage'[Stage] = "P") && 'PartStage'[Status] = "C")
Step2 code returns a table that has all the partIds where there is a Status = C and Stage = R || P. However, now we need to go through and make sure that for every Complete Release there is a matching Complete Production. We can do this through another summarize:
Step3 = SUMMARIZE(FILTER(SUMMARIZE('PartStage', 'PartStage'[partId], 'PartStage'[Stage], 'PartStage'[Status]), ('PartStage'[Stage] = "R" || 'PartStage'[Stage] = "P") && 'PartStage'[Status] = "C"), PartStage[partID], "Release&Production Complete", ("R" IN VALUES(PartStage[Stage])) && ("P" IN VALUES(PartStage[Stage])))
Step3 gives a table of distinct part IDs and a true/false value that indicates whether a part ID is "interesting". From here, we can count where Release&Production Complete = True to get the correct count:
Step4 = COUNTAX(FILTER(SUMMARIZE(FILTER(SUMMARIZE('PartStage', 'PartStage'[partId], 'PartStage'[Stage], 'PartStage'[Status]), ('PartStage'[Stage] = "R" || 'PartStage'[Stage] = "P") && 'PartStage'[Status] = "C"), PartStage[partID], "Release&Production Complete", ("R" IN VALUES(PartStage[Stage])) && ("P" IN VALUES(PartStage[Stage]))), [Release&Production Complete]), [Release&Production Complete])
Step 4 gives the correct value, but it's a VERY messy expression. A filtered summarization within a filtered summarization? This not only hurts to look at, but would be a pain to edit or for future people to upkeep. Let's reorganize it.
Step5 =
COUNTROWS( FILTER( SUMMARIZE( FILTER('PartStage', 'PartStage'[status]="C"), 'PartStage'[partId], "Release&Production Complete", ("R" IN VALUES('PartStage'[Stage])) && ("P" IN VALUES('PartStage'[Stage])) ), [Release&Production Complete] = TRUE() ) )
So by only using SUMMARIZE once and only grouping by partId, we're able to get to the True/False much more quickly. I also switched to use COUNTROWS, which should result in better performance, since it doesn't have to evaluate each row for a True/False value before counting it or not. This expression also looks much better, and though I'm sure it's possible to make it even cleaner, possibly using CALCULATE syntax, for now it gives the correct value for the test data I created, and will update to reflect current context in visuals.
I hope what I did here makes sense, and if you have any further questions or want more explanation about why I did something, feel free to follow up in this thread.
@Anonymous not sure if following measure will work without looking at data but give it a try, if not then send sample pbix file with expected result and will get you the solution
Manufacturing Complete = CALCULATE ( DISTINCTCOUNT('pmiscan PartStage'[partId]), ALLEXCEPT ( 'pmiscan PartStage','pmiscan PartStage'[partId] ), 'pmiscan PartStage'[partStageStage] = "C" )
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
That still left it blank,
I don't want to exclude Completes, I want to incorperate and count ID's that have been in both Release complete and Manufacturing complete,
The only thing I would exclude persay is part ID's that aren't in both status of complete for those two stages,
Let me see if I can get a sample.
Thank you'
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |