Search code examples
powerbidax

Function base on filter from separate table

I have two tables in Power BI connected by Plant between each other.

Plant

Plant Catalogue
Plant A A
Plant B B

Material

Plant Material
Plant A Mat A
Plant B Mat B
Sth 1 Sth 2
12 2
23 3

I want to create measure base on IF and "Catalogue" meaning IF(Catalogue=A, then STH 1 else STH2. Somehow I receive errors.

Not sure if it's important but data is filtered also by slicer (Plant).

I've tried two similar solutions, that ain't working

Actual price LC = CALCULATE(IF( FILTER(Plant, Plant[Catalogue] ="A"), Material[STH 1],Material[STH 2])

Second option

Actual price LC = VAR Price_catalogue = Plant[Catalogue] RETURN CALCULATE(IF( Price_Catalogue = ="A"), Material[STH 1],Material[STH 2])

Unfortunately it still recognize Plant[Catalogue] as dataset not as single value.

Solution

  • Try this measure:

    Actual price LC = 

VAR thisMat = MAXX(Plant, Plant[Catalogue])
VAR this1 = MAXX(Material, [Sth 1])
VAR this2 = MAXX(Material, [Sth 2])

RETURN IF(thisMat = "A", this1, this2)

    Example Output of Measure:

    Example Output