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.
I have two measures which calculate a value for a year. See following table.
My goal is to fill the gaps for measure B with the last available value. The result should look like:
Which DAX Construct can be used to solve my problem?
Solved! Go to Solution.
You may add an additional measure.
Measure = VAR y = SELECTEDVALUE ( Table1[Year] ) RETURN CALCULATE ( [Measure B], TOPN ( 1, FILTER ( ALLSELECTED ( Table1[Year] ), Table1[Year] <= y && NOT ( ISBLANK ( [Measure B] ) ) ), Table1[Year], DESC ) )
You may add an additional measure.
Measure = VAR y = SELECTEDVALUE ( Table1[Year] ) RETURN CALCULATE ( [Measure B], TOPN ( 1, FILTER ( ALLSELECTED ( Table1[Year] ), Table1[Year] <= y && NOT ( ISBLANK ( [Measure B] ) ) ), Table1[Year], DESC ) )
@v-chuncz-msft This just solved a big problem for me, thanks!
However, the measure is not giving me a total. I think I understand why it does not, but I don't know how to solve it.
I modified the formula in this way:
It is giving me exactly what I needed on a row level, but won't return a total. Is there a way to modify the measure?
Hi @TGS84
What is the code for your [Measure B]?
One way would be to recreate what happens in the matrix within the measure, i.e. by building a dynamic table with the years in the first column and measure B in the second column and then look for the LASTNONBLANK. But depending on the code for your measure it might be simpler than that. That is why i am asking you to show it.
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |