Material schedule adherence

[Locco]

Hi all,

I have a table with production data which includes production date, quantity, production machine, and part produced. I have another, master table, which shows each production machine and which specific parts should be built on which specific machines.

I'd like to compare the production table to the master table and if a part was built on a machine other than what is on the master table to be able to display a message such as "deviation." I've seen a few similar "if this data is on another table" problems/solutions but were not able to get those solutions to work.

Sample data is basically

Production table

Date	Production Machine	Part	Quantity
12/5/2021	Machine1	PartA	100
12/5/2021	Machine1	PartB	120
12/5/2021	Machine3	PartC	100
12/5/2021	Machine4	PartD	125
12/5/2021	Machine3	PartE	162
12/5/2021	Machine1	PartF	95

Master table

Production Machine	Part
Machine1	PartA
Machine1	PartB
Machine2	PartC
Machine2	PartD
Machine3	PartE
Machine3	PartF
Machine4	PartG
Machine4	PartH
Machine4	PartI

Thanks!

[amitchandak]

@Locco , New column Production table

if([Production Machine] = maxx(filter(Master , Master[Part] =Production[Part] ), Master[Product Machine]), true(), false()) 

[Locco]

Thanks @amitchandak , that does work but I ran into another problem.

The master table has a few parts which build on 2 different machines. For these, even though it ran on one of the two correct machines, it is displaying false instead of true.

Is there a modification to allow it to give a correct result with this in mind?

[v-yiruan-msft · Accepted solution]

Hi @Locco ,

I created a sample pbix file(see attachment), please check whether that is what you want.

Measure = 
VAR _selmpart =
    SELECTEDVALUE ( 'Master'[Part] )
VAR _tabm =
    CALCULATETABLE (
        VALUES ( 'Master'[Production Machine] ),
        FILTER ( 'Master', 'Master'[Part] = _selmpart )
    )
VAR _tabp =
    CALCULATETABLE (
        VALUES ( 'Production'[Production Machine] ),
        FILTER ( 'Production', 'Production'[Part] = _selmpart )
    )
RETURN  
    IF (
        ISEMPTY ( _tabp ),
        BLANK (),
        IF ( COUNTROWS( EXCEPT ( _tabm, _tabp ) )>0 , "deviation", "same" )
    )

Best Regards

[Locco]

Thanks @v-yiruan-msft , but I was unable to get the measure to work correctly.

I created a visual table with the production machines in a column and the parts in a column. I have about 30 rows, when I add the measure as a new column the results filter down to only 1 row. This is also the only machine/part combination which is unique, meaning only 1 part is built on that machine. For the other machines which build multiple parts, they were removed or on another table they have a blank, only the 1 part is showing "same."

Also, in the screenshot you provided the measure is returning an incorrect value. "Part B" should say "same," and not "deviation."

[v-yiruan-msft]

Hi @Locco ,

Did you create any relationship between Master and Production? It is not required to create any relationship between them. If it is possible, could you please share your simplified sample pbix file (exclude sensitive data) with me in order to make troubleshooting and provide you a suitable solution?

In addition, for Part B in my sample pbix file, there are two machines Machine1 and Machine2 for Part B in table Master, but there is only one Machine1 in table Production. So the returned value for the measure is "deviation". If my understanding is wrong, please feel free to correct me with more details. Thank you.

Best Regards

[Locco]

Thanks again @v-yiruan-msft ,

After some further experimentation I believe I got it to work. I will need to so some larger testing over the weekend to confirm, but I will check back in to let you know.

Thanks for your help!