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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Please help Dax Formula

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 

6 REPLIES 6
Cmcmahan
Resident Rockstar
Resident Rockstar

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") )
)
Anonymous
Not applicable

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,

 

power bi example pt 2.PNGExample power BI.PNG

 

 

@Cmcmahan @parry2k 

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:

  • This measure should count items in the PartStage table
  • It should only count each distinct PartId once
  • It should only count a part ID if it has BOTH an entry for PartStageStage = R AND PartStageStage = P.
  • For those two required entries, they must BOTH have the partStageStatus = C.
  • We can completely ignore the existance/status of any rows indicating a Stage of A, I, or R, we're only interested in R&P statuses.

 

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.

 

parry2k
Super User
Super User

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

Anonymous
Not applicable

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'

Anonymous
Not applicable

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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