Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all, hopefully a simple question here. I have a percent value for each year-quarter pair, shown below. Users are forced to filter on a single year, and can optionally filter one or more quarters. If one quarter is chosen, the VI% is correct. If multiple are selected, they sum, which isn't correct.
What we need is for the "latest" (year-quarter) value we have to display if quarter has more than one value.
E.g. if Q1 is selected, display 67%, if Q2 is selected display 2.45%, if more than one is selected, display 2.45%. If, down the line, Q3 is added, multiple selections should display that value.
The first part is obvious:
IF(HASONEVALUE(table[YearQuarterKey]), VI%, TBD)
but I'm unsure how to write the expression that would return the rest. I tried to pull the actual latest YearQuarterKey value with:
IF(HASONEVALUE(table[YearQuarterKey]),VI%, CALCULATE(VI%, table[YearQuarterKey] = MAX(YearQuarterKey)))
but that didn't work. I also tried LASTNONBLANK and LASTNONBLANKVALUE but wans't able to figure out the syntax to get it to do what I need.
Any advice?
Solved! Go to Solution.
@RMDSPC check if it helps Avoid pitfalls when using LASTNONBLANKVALUE and LASTNONBLANK functions - leads to incorrect results ...
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@RMDSPC check if it helps Avoid pitfalls when using LASTNONBLANKVALUE and LASTNONBLANK functions - leads to incorrect results ...
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@RMDSPC could you provide more details, on how your data looks like, and what are the calculations? Read this post to get your answer quickly.
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hi @parry2k, thanks for the repsonse. For the purposes of the example, I believe all the fact data we need is basically as provided. There are not currently other measures in this table, other than the one I'm trying to create.
Fact_table
Key 1 | Key 2 | YearQuarterKey | VI% |
7096 | 7105 | 20231 | x% |
7096 | 7015 | 20232 | y% |
This fact table is connected to my Dim.Date by a bridge that contains YearQuarterKey, and the Year and Quarter slicers I mentioned are controlled through the Year and Quarter columns in that table.
For a given parent and child, the data looks as below. When the Quarter field is in the matrix as an example, you can see the two VI% values.
When the quarter hierarchy is removed, it looks like this, which is how it will look to the user. In this case, both quarters 1 and 2 are selected, but I want that value to display as 2.5%, since that is our "latest" VI% value, rather than summing to 70.1%
User | Count |
---|---|
93 | |
85 | |
78 | |
68 | |
63 |
User | Count |
---|---|
113 | |
99 | |
97 | |
64 | |
59 |