Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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]))
)
@Anonymous , No very clear , Try a measure like
countx(filter(summarize(table, Table[Plant], Table[Material],"_1" ,count(Table[Material])),[_1]>=2),[Material])
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
40 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
44 | |
32 | |
30 | |
18 | |
17 |