Compare Different Rows of Table Based on Column Value

[Anonymous]

I am limited to using DAX only at this point for a solution.  I have a giant table with plant data (e.g. 1111, 1112, etc.) and need to compare the "owning plant" vs. the other plant(s).  So a table like the below where I have to compare line 1 status against line 2 status since they're the same material and line 3 doesn't have a corresponding one in another plant so it can just return blank, null, whatever.

Is it better to do a whole calculated table for every plant and link them in the relationships or use filtering to look for parts that exist in the other plant within a formula?  See sample table below and example formula.  Thanks.

Sample Table

------------------------------------

Plant    Material    Status 

1111     1234         50

1112     1234         40

1111     2345         50

------------------------------------

The below works for me to get parts that exist in both plants, so I can modify that, but I had to come up with the formula myself so it may be hideous and inefficient.  Maybe something better than selecting columns to get material + plant, then selecting columns again just to get the material list only?

FILTER('Master Data', 'Master Data'[mara_material_number] IN

DISTINCT(SELECTCOLUMNS(FILTER(SELECTCOLUMNS('Master Data',"Material", 'Master Data'[mara_material_number], "Plant", 'Master Data'[marc_plant]), [Plant]="1148"),"Material",[Material]))

)

[amitchandak]

@Anonymous , No very clear , Try a measure like

countx(filter(summarize(table, Table[Plant], Table[Material],"_1" ,count(Table[Material])),[_1]>=2),[Material])

[Anonymous]

Sorry, what I need to get at is to see a line for material 1234 in plant 1111 which shows me the status 50 and then also that it exists in plant 1112 with a status of 40.  So it's not totalling up the data, it's looking at data from another line for the same material.  Put another way, I want to see information about other lines based on one column (material) on a given line.