Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I have a situation below in Power BI and DAX language.
I have 2 simple tables:
CountryTable
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.
Solved! Go to Solution.
This measure will work for you.
Measure =
IF(
ISEMPTY(CountryTable),
BLANK(),
CALCULATE ( MAX ( YearTable[Year] ), ALLSELECTED ( YearTable[Year] ) )
)
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 😎
thanks very much
I have an extension of this question posted here:
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):
Hi,
You may download my PBI file from here.
Hope this helps.
@Ashish_Mathur Thanks very much. The .pbix file is not downloadable. Can you upload again?
see attaced screen print.
You dont need the YearTable
You could simply have
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 !
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.
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.
@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
I have an extension of this question posted here:
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
112 | |
92 | |
87 | |
32 | |
27 |