Select Value based on multiple columns

[KrisG]

Hi guys,

I hope you can help me find a solution to this case. The result that I want is the FINAL AMOUNT column. Thanks a lot! I could've done this in the Power Query editor but I am using data connected to Azure via Direct Query.

[v-xulin-mstf]

Hi @KrisG,

Try measure as:

Final Amount = 
IF(
    MAX('Table'[Offer is Selected])="True",
    MAX('Table'[Amount]),
    IF(
        MAX('Table'[Amount])=0 &&
        COUNTROWS(FILTER(
            ALL('Table'),
            'Table'[Project Name]=MAX('Table'[Project Name]) 
        ))=1,
        0,
    MINX(
        FILTER(
            ALL('Table'),
            'Table'[Project Name]=MAX('Table'[Project Name]) && 'Table'[Amount]>0
        ),
        'Table'[Amount]
    )
)
)

Here is the output:

The pbix is attached.

If you still have some question, please don't hesitate to let me known.‌‌

Best Regards,

Link

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

[KrisG]

Hi @v-xulin-mstf,

Sorry for the late reply and thanks for the help. Anyways, I tried the measure and it still doesn't give me the right result.

Looking at your ouput, all seems to be right except for Project A. If a project has TRUE and FALSE "Offer is Selected", the final amount should be the TRUE offer only.  Instead of Final Amount as (1, 2, 1), it should be (2) only.

[Fowmy]

@KrisG 

Do you need a measure or a calculated column?

[KrisG]

Hi @Fowmy , is it possible to have both options? If not, then a measure will be great.

[Fowmy]

@KrisG 

The Measure:

Final Amount M = 
var __proj = SELECTEDVALUE(Table2[Project Name])
var __amount = SELECTEDVALUE(Table2[Amount])
var __offerselected = SELECTEDVALUE(Table2[Offer is Selected])
return

SWITCH(
    TRUE(),
    __offerselected = TRUE() , __amount,
    ISEMPTY(
        FILTER(
            ALLSELECTED(Table2),Table2[Project Name]=__proj &&  Table2[Offer is Selected]=TRUE()
        )
    ),
    if(
        __amount = 
        MINX(
            FILTER(
                 ALLSELECTED(Table2),Table2[Project Name]=__proj &&  Table2[Offer is Selected]=FALSE() && Table2[Amount] > 0
            ),
            Table2[Amount]
        ),
        __amount
    ) 
)   

Calculated Column:

Final Amount = 
var __proj = Table2[Project Name] 
var __amount = Table2[Amount] 
var __offerselected = Table2[Offer is Selected] 

return

SWITCH(
    TRUE(),
    __offerselected = TRUE() , __amount,
    ISEMPTY(
        FILTER(
            Table2,Table2[Project Name]=__proj &&  Table2[Offer is Selected]=TRUE()
        )
    ),
    if(
        __amount = 
        MINX(
            FILTER(
                Table2,Table2[Project Name]=__proj &&  Table2[Offer is Selected]=FALSE() && Table2[Amount] > 0
            ),
            Table2[Amount]
        ),
        __amount
    ) 
)   

[KrisG]

Hi @Fowmy thanks for the codes but the result is incorrect. I think it has to do with the MINX function.

Please take into consideration that a project can have more than one offer and an offer can have more than one technique.

Here's the code with just minor label changes from my actual query.

[Fowmy]

@KrisG 

Sorry, not clear enough. provide sample data as you did but including more records with the scenarios you just mentioned along with the expected results.