Forum Discussion
STC-EDR
1 year agoNew Member
Help on DAX to compute differences for multiple variables
Hi, I am struggling to come up with a DAX solution to compute the differences of multiple selected forecast versions. First, I will select two forecast versions on my slicers, which will be shown...
- 1 year ago
Hi,
I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file.
INDEX function (DAX) - DAX | Microsoft Learn
expected result measure: = VAR _diff = CALCULATE ( SUM ( forecast[Value] ), INDEX ( 1, ALLSELECTED ( forecast[Version], forecast[Version sort] ), ORDERBY ( forecast[Version sort], ASC ) ) ) - CALCULATE ( SUM ( forecast[Value] ), INDEX ( 1, ALLSELECTED ( forecast[Version], forecast[Version sort] ), ORDERBY ( forecast[Version sort], DESC ) ) ) VAR _result = CALCULATE ( SUM ( forecast[Value] ), KEEPFILTERS ( forecast[Version] IN VALUES ( 'version'[Version] ) ) ) RETURN IF ( HASONEVALUE ( 'version'[Version] ), SWITCH ( TRUE (), COUNTROWS ( VALUES ( forecast[Version] ) ) = 2, IF ( SELECTEDVALUE ( 'version'[Version] ) = "Difference", _diff, _result ) ) )
Jihwan_Kim
1 year agoSuper User
Hi,
I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file.
INDEX function (DAX) - DAX | Microsoft Learn
expected result measure: =
VAR _diff =
CALCULATE (
SUM ( forecast[Value] ),
INDEX (
1,
ALLSELECTED ( forecast[Version], forecast[Version sort] ),
ORDERBY ( forecast[Version sort], ASC )
)
)
- CALCULATE (
SUM ( forecast[Value] ),
INDEX (
1,
ALLSELECTED ( forecast[Version], forecast[Version sort] ),
ORDERBY ( forecast[Version sort], DESC )
)
)
VAR _result =
CALCULATE (
SUM ( forecast[Value] ),
KEEPFILTERS ( forecast[Version] IN VALUES ( 'version'[Version] ) )
)
RETURN
IF (
HASONEVALUE ( 'version'[Version] ),
SWITCH (
TRUE (),
COUNTROWS ( VALUES ( forecast[Version] ) ) = 2, IF ( SELECTEDVALUE ( 'version'[Version] ) = "Difference", _diff, _result )
)
)