Microsoft Power BI - DAX - tricky situation involving ALLSELECTED in slicer

[snph1777]

I have a situation below in Power BI and DAX language.

I have 2 simple tables:

1. CountryTable

2. YearTable

There is a 1-M relationship between YearTable and CountryTable.

The latter (Year) is used to feed values into a slicer.

The former (Country) is the main table, with just 4 rows.

These two tables are related via the Year column.

The Year slicer always has EXACTLY 2 values chosen in my Power BI report.

I need the Maximum of these two values of the year slicer as a measure, for each row of my visual.

At the same time, these two year values of the slicer must remove the unwanted rows in my report visual, based on the slicer selection of year values.

For example, when the slicer has 2019 and 2020 chosen, I need the value as in the DesiredOutput1 page.

Similarly, you can see DesiredOutput2 (Slicer values are 2020 and 2022);   DesiredOutput3 (Slicer values are 2019 and 2022) pages.

I tried something like this:

Max_Year_Measure = MAXX(

                          ALLSELECTED(YearTable),

                          YearTable[Year]

                        )

One main requirement: the Year column of my main visual must come from YearTable, not from CountryTable;
hence both the Year columns (one in the slicer, the other in the visual) are from YearTable only;
this is a requirement, because I am using some RANKX function to filter out all rank values after 1,
based on the slicer selection.

You can see this below:

Rank_FF_ASC_Measure = IF(

                              HASONEVALUE(YearTable[Year]) = TRUE,

                                  VAR Ranking = RANKX(

                                                       ALLSELECTED(YearTable[Year]),

                                                       CALCULATE(MAX(YearTable[YearOrder])),

                                                        ,

                                                       1,

                                                       SKIP

                                                     )

                                   RETURN Ranking,

                               BLANK()

                           )

Note:

In my client dataset, the Year values are prefixed with values such as Q1-2022, Q2-2022, etc. Hence I need to use  YearTable[YearOrder] as the main sort column.

My eventual goal is to attain this visual below (when 2019 and 2020 are chosen in the slicer):

Or can [Rank_FF_ASC_Measure] be modified to meet my requirement ?

Any suggestion.

Please use the .pbix file in this posting. Feel free to reach out if you have questions.

[speedramps]

I personally prefer 1 problem per ticket.

Members get a quicker reply and each solver gets the kudos they reserve.

If you add 2 of 3 problems and solvers to a single case the it gets messy and only the final solvers gets the kudos.

Super Users try avoid clashes, so I will backout and leave you in the hands of Ashish Mathur. 

Ok 😎

[snph1777]

@speedramps 

thanks very much

I have an extension of this question posted here:

https://community.powerbi.com/t5/Desktop/Microsoft-Power-BI-tricky-ALLSELECTED-with-2-values-in-slic...

[lasersharks · Accepted solution]

This measure will work for you.

Measure = 
IF(
    ISEMPTY(CountryTable), 
    BLANK(),
    CALCULATE ( MAX ( YearTable[Year] ), ALLSELECTED ( YearTable[Year] ) )
)

[snph1777]

@lasersharks thanks very much

I have an extension of this question posted here:

https://community.powerbi.com/t5/Desktop/Microsoft-Power-BI-tricky-ALLSELECTED-with-2-values-in-slic...

[snph1777]

@lasersharksthanks very much;

Your measure works fine; but one issue (I did not post this issue in my question earlier).

I am further developing a measure like this (the .pbix file has been updated too):

Rank_FF_ASC_Measure =  IF(

                              HASONEVALUE(YearTable[Year]) = TRUE,

                                  VAR Ranking = RANKX(

                                                       ALLSELECTED(YearTable[Year]),

                                                       CALCULATE(MAX(YearTable[YearOrder])),

                                                        ,

                                                       1,

                                                       SKIP

                                                     )

                                   RETURN Ranking,

                               BLANK()

                           )

My eventual goal is to attain this visual below (when 2019 and 2020 are chosen in the slicer):

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[snph1777]

@Ashish_Mathur Thanks very much. The .pbix file is not downloadable. Can you upload again?

[Ashish_Mathur]

You are welcome.  File attached.

[speedramps]

see attaced screen print.

You dont need the YearTable
You could simply have

MaxYear =

CALCULATE(

MAX(CountyTable[Year]),

ALL(CountyTable)

)

If you insist on having the YearTable with 1:M relationship to CountyTable

then this will also work

MaxYear =

CALCULATE(

MAX(CountyTable[Year]),

ALL(YearTable)

)

The CACULATE and ALL command overide the visual row context.

Your attempt does not work because the it does not overide the visual context.

I recommed you do an online tutorial about visual row and column filter context and how to override it.

Thanks for reaching out for help.

I have helped you, now please help me by giving kudos.

Remeber we are unpaid volunteers.

Click the thumbs up and accept as solution button. 

One question per ticket please. If you need to extend your request then please raise a new ticket.

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you !

[snph1777]

The relationship between the 2 tables is 1-M; am not getting the right output with the second option; also I need the Year column of the visual, from the YearTable that also feeds the slicer, NOT from the CountryTable.

[snph1777]

@speedramps      thanks very much, will get back shortly

[speedramps]

At least 3 solvers have responded, so I will drop out of this chat to help someone else.

Ashish Mathur is excellent seupper user. I dobt know lasersharks yet.

If you need me then just quote @speeramps in the chat.

Good luck everybody ! 😎

[Ashish_Mathur]

Thank you for your kind words @speedramps.  Your solutions are very good as well.  Let's just continue learning from each other.

[snph1777]

@Ashish_Mathur   thanks very much.

I have an extension of this question posted here:

https://community.powerbi.com/t5/Desktop/Microsoft-Power-BI-tricky-ALLSELECTED-with-2-values-in-slic...

[snph1777]

@speedramps     you can stay here; no issue;   @lasersharks  solution works fine, but as I have mentioned, I have an additional requirement (it is my fault not to have posted the entire issue yesterday itself). Ashish's files are not accessible. If you can provide a solution, and get my [UltimateDesiredOutput1], it would be great

[snph1777]

I have an extension of this question posted here:

https://community.powerbi.com/t5/Desktop/Microsoft-Power-BI-tricky-ALLSELECTED-with-2-values-in-slic...