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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!