Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
snph1777
Helper V
Helper V

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

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.

c12.png

 

y1.png

 

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.

 

do1.png

 

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

 

do2.png

 

do3.png

 

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):

 

dot.png

 

dot99.png

 

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.

1 ACCEPTED SOLUTION
lasersharks
Kudo Collector
Kudo Collector

This measure will work for you.

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

 

View solution in original post

16 REPLIES 16
speedramps
Super User
Super User

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 😎

 

 

lasersharks
Kudo Collector
Kudo Collector

This measure will work for you.

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

 

@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):

 

dot.png

 

dot99.png

 

Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

You are welcome.  File attached.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
speedramps
Super User
Super User

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 !

 
speedramps_0-1655845281410.png

 

 

 

 

 

 

 

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.

@speedramps      thanks very much, will get back shortly

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 ! 😎

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.