cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KrisG
Frequent Visitor

Select Value based on multiple columns

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.

 

KrisG_0-1625387089969.png

 

 

7 REPLIES 7
v-xulin-mstf
Community Support
Community Support

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:

vxulinmstf_0-1625724403153.png

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!

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
Super User
Super User

@KrisG 

Do you need a measure or a calculated column?

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

KrisG
Frequent Visitor

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

@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
    ) 
)   
Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

KrisG
Frequent Visitor

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.

 

KrisG_0-1625399226259.png

 

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

KrisG_1-1625399381383.png

 

@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. 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!