Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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 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:
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?
Hi,
You may download my PBI file from here.
Hope this helps.
File is not downloading; can you upload again?
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.
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.
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.
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |