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

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

Locco's avatar
Locco
Helper III
4 years ago

Thanks Anonymous , 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."

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      Locco
      Helper III
      4 years ago

      Thanks again Anonymous ,

       

      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!