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

user_3812887 0
Viewed 277

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.

enter image description here

enter image description here

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.

enter image description here

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

enter image description here

enter image description here

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

enter image description here

enter image description here

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.

slicers dax powerbi-desktop powerbi measure
3 Answers
user_183450370

This code will do what you need.

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

Here is my solution.

First, I've modified the relationship between YearTableand CountryTable. In your file, the relationship is a bi-directional many-to-many.

I changed it to something more typical. It's a one-to-many from YearTable to CountryTable.

something more typical

In order to simplify the context. Here, YearYable filters CountryTable and not the other way around. Meaning that when you are in the row context of CountryTable, all selected rows in YearTable remains.

My first guess was to go for a simple measure like this:

MaxYear1 = 
CALCULATE(
    MAX(YearTable[Year]),
    ALLSELECTED(CountryTable[Year])
)

But you don't get the expected result at visual level. Since it's able to calcule a value even for unselected rows, it returns them still.

MaxYear1

Note: I'm positive there is a proper workaround for this but couldn't find one.

So, I made a new measure:

MaxYear = 
MAXX(
    ADDCOLUMNS(
        SUMMARIZE(
            CountryTable,
            CountryTable[Year]
        ),
        "MYear",
        MAX(YearTable[Year])
    ),
    [MYear]
)

The ADDCOLUMNS/SUMMARIZE is a well documented practice of working a context by creating a kind of view within a measure. Maybe not it's best use here, but it works.

MaxYear

user_58872670
Related