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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
snph1777
Helper V
Helper V

Microsoft Power BI - tricky ALLSELECTED with 2 values in slicer, but show only TOP 1 row in visual

This question is an extension of an already answered question, which I posted this week.

 

I have the below situation in Microsoft Power BI.

 

I have 2 simple tables:

 

1) CountryTable

2) YearTable

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

 

The latter (YearTable) is used to feed values into a slicer. (In my client database, Year has some alphabetical prefixes, such as Q1-2022, so I prefer to use YearOrder column to sort the Year column at the backend, while the slicer will display the Year column.)

The former (CountryTable) is the main table, with just a few sample rows.

 

yt.pngct1.png

 

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.

 

doo1.png

 

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

d002.png

 

doo3.png

 

I have indeed successfully obtained DesiredOutput1, DesiredOutput2, DesiredOutput3. Thanks to all the folks who helped me attain this.

 

Now, my main requirement in this posting, is this:

 

After obtaining the DesiredOutputs above, I need the following output:

 

Show only the TOP 1 row (ASC order of Year column, which is the minimum value of the slicer).

 

Essentially:

 

Year column of the visual:                      Minimum value of the slicer

 

MaxYear_Measure_SlicerSelection:         Maximum value of the slicer (maximum of the two values chosen in the slicer)

 

You can see below:

 

xrxr.png

 

Note: MaxYear_Measure_SlicerSelection measure can refer to any one of the two measures [MaxYear] or [MaxYearMeasure_Community] (see the .pbix file for the formulas of the measures).

Any idea ?

 

I prefer the Year column of the visual not to be converted to a new measure. Would RANKX help in this case ? Any thoughts?

8 REPLIES 8
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/

File is not downloading; can you upload again?

File attached.


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

Thanks Ashish.  Instead of [Measure 2], is it possible to use a column of one of the two tables ?

You are welcome.  No it is not.


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

I am looking to use a column for Year, rather than [Measure 2].

 

I am using a CALCULATE statement in CountryTable to change the filter context of the Year column to [Measure] value, then develop new metrics.

 

If Year column becomes  [Measure 2], not sure how I can use CountryTable as a base.

 

Any idea? Can we use some RANKX function to keep only the earliest year in the Year column of the visual, while the [Measure] will show the latest year.  Something like this, without the usage of [Measure 2].

Not sure.  May be someone else will help you.


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

Thanks Ashish for your kind help; I can always create another YearTable to achieve my end goal, but was seeing whether it can be achieved via columns, without a new table.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.