Forum Discussion

Locco's avatar
Locco
Helper III
4 years ago
Solved

Material schedule adherence

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

DateProduction MachinePartQuantity
12/5/2021Machine1PartA100
12/5/2021Machine1PartB120
12/5/2021Machine3PartC100
12/5/2021Machine4PartD125
12/5/2021Machine3PartE162
12/5/2021Machine1PartF95

 

Master table

Production MachinePart
Machine1PartA
Machine1PartB
Machine2PartC
Machine2PartD
Machine3PartE
Machine3PartF
Machine4PartG
Machine4PartH
Machine4PartI

 

Thanks!

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

6 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Locco , New column Production table

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

    • Locco's avatar
      Locco
      Helper III
      4 years ago

      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?

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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