Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I need to get the last CUMULATIF value for each single NO_PALETTE filterd by the choosen date. The date slicer is coming from a calendar table. Then I want to sum all the value the get the total. I tried this measure but it don't give me the total:
Qty(filter)_test =
var dateselect = SELECTEDVALUE(Dimdate[Date])
var minindex = CALCULATE(MIN(HIST_PAL[Index]),HIST_PAL[DATE] <= dateselect,ALLEXCEPT(HIST_PAL,HIST_PAL[NO_PALETTE]))
Return
CALCULATE(SUM(HIST_PAL[CUMULATIF]),HIST_PAL[Index] = minindex)
For September 22nd 2021 it should be 612.
For Septembre 23td 2021 it should be 1052.
Thank you!
Here is the PBIX file: https://www.dropbox.com/s/5eg1o2nng8loitp/LastValue.pbix?dl=0
Solved! Go to Solution.
@FRG Your measures needs to sum up the across all distinct values of NO_PALETTE, like this:
Qty(filter)_test =
SUMX(
VALUES(HIST_PAL[NO_PALETTE]),
var dateselect = SELECTEDVALUE(Dimdate[Date])
var minindex = CALCULATE(MIN(HIST_PAL[Index]),HIST_PAL[DATE] <= dateselect,ALLEXCEPT(HIST_PAL,HIST_PAL[NO_PALETTE]))
Return
CALCULATE(SUM(HIST_PAL[CUMULATIF]),HIST_PAL[Index] = minindex)
)
Tu peux corriger ta mesure comme ça,
=
VAR dateselect = SELECTEDVALUE( Dimdate[Date] )
RETURN
SUMX(
VALUES( HIST_PAL[NO_PALETTE] ),
VAR minindex =
CALCULATE(
MIN( HIST_PAL[Index] ),
HIST_PAL[DATE] <= dateselect,
ALLEXCEPT( HIST_PAL, HIST_PAL[NO_PALETTE] )
)
RETURN
CALCULATE( SUM( HIST_PAL[CUMULATIF] ), HIST_PAL[Index] = minindex )
)
mais elle marche pas assez efficacement; voici une mesure 30+ fois plus vite (selon DAXStudio, 15ms vs 500ms) bien qu'elle ait l'air bcp plus compliquée.
=
VAR dateselect = SELECTEDVALUE( Dimdate[Date] )
RETURN
SUMX(
VALUES( HIST_PAL[NO_PALETTE] ),
CALCULATE(
SUM( HIST_PAL[CUMULATIF] ),
KEEPFILTERS(
TOPN(
1,
CALCULATETABLE(
HIST_PAL,
HIST_PAL[DATE] <= dateselect,
ALLEXCEPT( HIST_PAL, HIST_PAL[NO_PALETTE] )
),
HIST_PAL[Index], ASC
)
)
)
)
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
@FRG Your measures needs to sum up the across all distinct values of NO_PALETTE, like this:
Qty(filter)_test =
SUMX(
VALUES(HIST_PAL[NO_PALETTE]),
var dateselect = SELECTEDVALUE(Dimdate[Date])
var minindex = CALCULATE(MIN(HIST_PAL[Index]),HIST_PAL[DATE] <= dateselect,ALLEXCEPT(HIST_PAL,HIST_PAL[NO_PALETTE]))
Return
CALCULATE(SUM(HIST_PAL[CUMULATIF]),HIST_PAL[Index] = minindex)
)
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 | |
102 | |
84 | |
79 | |
70 |
User | Count |
---|---|
120 | |
110 | |
95 | |
82 | |
77 |