Measure to receive data base on filters
I have following tables
Plant
| Plant | Name |
|---|---|
| 1111 | Test 1 |
| 2222 | Test 2 |
Material
| Material | Plant | Index | Date |
|---|---|---|---|
| 1234 | 1111 | Aluminum premiumEurope | 31.01.2024 |
| 1234 | 2222 | Aluminum premiumEurope | 31.01.2024 |
| 1334 | 1111 | CopperNorth America | 31.01.2024 |
| 1334 | 2222 | CopperNorth America | 31.01.2024 |
RM Prices
| Price | Budget | Index | Date |
|---|---|---|---|
| 10.00 | 11.11 | Aluminum premiumEurope | 31.01.2024 |
| 11.00 | 11.11 | Aluminum premiumEurope | 31.01.2024 |
| 20.00 | 22.22 | CopperNorth America | 31.03.2024 |
| 30.00 | 22.22 | CopperNorth America | 31.03.2024 |
| 40.00 | 11.11 | Aluminum premiumEurope | 31.03.2024 |
| 60.00 | 11.11 | Aluminum premiumEurope | 31.03.2024 |
Reporting date
| Date |
|---|
| 31.01.2024 |
| 31.03.2024 |
Slicers are made based on Reporting date and Plant columns.
I want to compare if Index in "Material" table appears in "RM Prices". In both tables Index is not unique in general, but after filtering base on Date in "RM Prices" and Plant in "Material" we receive unique Index in both tables.
I've tried to create measure
Miara =
VAR MAT_material = MAXX('Material', 'Material'[Index])
VAR RAW_material = MAXX('RM prices','RM prices'[Index])
VAR Reporting_Date = MAXX('Reporting date','Reporting date'[Date])
RETURN(
LOOKUPVALUE('RM prices'[Index], 'RM prices'[Index],MAT_material,'RM prices'[Date],Reporting_Date,))
Unfortunately it didn't work. I assume that this is due to MAT_material is not unique but I'm not sure how implement additional filter.
You may need to review your model.
You can try this measure :
Miara =
VAR MAT_Material = SELECTEDVALUE('Material'[Index])
VAR Reporting_Date = MAX('Reporting date'[Date])
RETURN
IF(
NOT(ISBLANK(MAT_Material)) &&
COUNTROWS(
FILTER(
'RM Prices',
'RM Prices'[Index] = MAT_Material && 'RM Prices'[Date] = Reporting_Date
)
) > 0,
"Match Found",
"No Match"
)
The MAT_Material stores the currently selected value of Index from the Material table while the Reporting_Date stores the selected reporting date from the slicer. Then I tried to filter the RM Prices to get rows where the Index matches the MAT_Material and the Date matches the selected reporting date.
