Retrieve values of rows from different criteria of column

[dimk23]

Hello valuable supporters,

a little boost please because i stacked in DAX 🙂 ,

the final aim is to show up in my Visual (last photo) the values ( cost per kilos) of Depart 1 in rows where belonged to other Departms. The logic behind this is the Depart 1 is the initial phase and doesnt have colours, the colours show up in the following Departs (2,3 etc). For all other departm (2,3 etc) it must be visualised with cost prices of Depart 1. I tried with Filter fuctions (ALL,selectedvalues etc) but something missing because i dont have clear mind for sure 🙂 Any idea please

Table Costing

Products

Departments

Visual

[v-cazheng-msft · Accepted solution]

Hi @dimk23,

You may try this Measure.

CostVal =
VAR InitialCost =
    CALCULATE (
        MIN ( 'Table Costing'[cost] ),
        FILTER (
            ALLEXCEPT ( 'Table Costing', 'Table Costing'[Main products] ),
            'Table Costing'[Department] = "Department 1"
        )
    )
VAR sumCost =
    CALCULATE (
        SUM ( 'Table Costing'[cost] ),
        FILTER ( Departments, 'Departments'[Department] = "Department 2" )
    )
RETURN
    IF (
        ISBLANK ( sumCost ),
        IF (
            MAX ( 'Table Costing'[Colour 1] ) <> "X-0000"
                && MAX ( 'Table Costing'[Colour 2] ) <> "X-0000"
                && MAX ( Departments[Department] ) = "Department 1",
            InitialCost,
            ""
        ),
        sumCost
    )

The result looks like this.

Also, attached the pbix file.

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please let me know. Thanks a lot!

Best Regards,

Community Support Team _ Caiyun

[v-cazheng-msft · Accepted solution]

Hi @dimk23,

You may try this Measure.

CostVal =
VAR InitialCost =
    CALCULATE (
        MIN ( 'Table Costing'[cost] ),
        FILTER (
            ALLEXCEPT ( 'Table Costing', 'Table Costing'[Main products] ),
            'Table Costing'[Department] = "Department 1"
        )
    )
VAR sumCost =
    CALCULATE (
        SUM ( 'Table Costing'[cost] ),
        FILTER ( Departments, 'Departments'[Department] = "Department 2" )
    )
RETURN
    IF (
        ISBLANK ( sumCost ),
        IF (
            MAX ( 'Table Costing'[Colour 1] ) <> "X-0000"
                && MAX ( 'Table Costing'[Colour 2] ) <> "X-0000"
                && MAX ( Departments[Department] ) = "Department 1",
            InitialCost,
            ""
        ),
        sumCost
    )

The result looks like this.

Also, attached the pbix file.

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please let me know. Thanks a lot!

Best Regards,

Community Support Team _ Caiyun

[dimk23 · Accepted solution]

Thanks v-cazheng-msft. Thats perfect 

[dimk23]

If you deduct the MAX of for colors 1 and 2 would be exactly that i wanted in my report. thank you again for you time.

___RETURNIF (ISBLANK ( sumCost ),IF (MAX ( Departments[Department] ) = "Department 1",InitialCost,""),sumCost)

[dimk23]

The color 1 and 2 means that, the product had painted with two colors and may with one color (the X-0000 means no color as you mentioned)  in Departm 2. I dont wont to feel with other color, i want to show up the cost value from the earliest Departm 1.

[vapid128]

add colnum

Color 1 Initial = "x-0000"

Color 2 Initial = "x-0000"

[vapid128]

When a product get both color 1 and color 2 x-0000 means no color.

The product with no color need a color from other line with same name of product name.

If every product with same name has same color 1/ color 2 and the name all like x-4 digits numbers, there is a easy way to make it work.

add colnum 

color 1 new = calculate(max([Color 1], ALLEXCEPT([Main Product]))

color 2 new = calculate(max([Color 2], ALLEXCEPT([Main Product]))