Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi people,
I am in a bit of a puzzle here and hope you guys can help:
I have the following table showing our forecast version:
Our forecast process works in the following way:
On the first day, each month, we get a new "SOP Version" and "LAG 1". After some days the "latest" LAG-version changes from "LAG 1" to "LAG 0" for the rest of the month.
Example in table: Latest "SOP Version"=SOP5. From "day 1" to fx. "day 4" the table will be loaded with "LAG 1" only. On "day 5" the table will be enriched with "LAG 0".
Now, what I want to make is a dynamic filter, which always filters the latest "SOP" and "LAG"-version on the Power BI report I have.
I have managed to make a summarized table in Power BI filtering the "lowest" LAG value for each SOP-version:
SOP&LAG (Latest Vers) = SUMMARIZE('Forecast';'Forecast'[SOP Version];"LAG (lowest value)";MINX('Forecast';'Forecast'[LAG]))
But I can't figure out to isolate the the latest SOP Version (SOP5) with its latest LAG-version (LAG0).
I am not even sure this is the right approach?
All ideas will be greatly appreciated.
Thanks.
Br,
Jakob
Solved! Go to Solution.
Hi @jayjay0306 ,
You could share your expected result here with image or table.
And you could try the following DAX:
Table 2 =
SUMMARIZE (
'Forecast',
'Forecast'[SOP Version],
"LAG (lowest value)", CALCULATE (
SELECTEDVALUE ( Forecast[LAG] ),
FILTER (
Forecast,
RIGHT ( Forecast[LAG], 1 ) = MINX ( Forecast, RIGHT ( Forecast[LAG], 1 ) )
)
)
)
Hi @jayjay0306 ,
You could share your expected result here with image or table.
And you could try the following DAX:
Table 2 =
SUMMARIZE (
'Forecast',
'Forecast'[SOP Version],
"LAG (lowest value)", CALCULATE (
SELECTEDVALUE ( Forecast[LAG] ),
FILTER (
Forecast,
RIGHT ( Forecast[LAG], 1 ) = MINX ( Forecast, RIGHT ( Forecast[LAG], 1 ) )
)
)
)
thanks v-eachen-msft, it got me the rest of the way 🙂
Br,
JayJay0306
@jayjay0306 , can we create a desc Rank on version and use Rank =1 to filter
For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
97 | |
79 | |
67 | |
60 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |