topic Re: Sum Switch Values in DAX Commands and Tips

Sum Switch Values

dcrow5378 — Mon, 20 Dec 2021 19:41:40 GMT

I have a measure that looks at the Value of the current month and compares it to the previous month and applies a switch function if higher than the previous month. I would like to be able to sum the values from this switch function.

Measure =

VAR PriorMonthAmt = CALCULATE( [Max FAP] , PARALLELPERIOD ( 'QM'[Report Month],-1,MONTH ) )

VAR Compare = IF ( ISBLANK ( PriorMonthAmt ), BLANK(), PriorMonthAmt - [Max FAP])

RETURN

SWITCH (

TRUE(),

Compare = 0, 0,

Compare < 0, 1,

Compare > 0, 0

)

Current Results: (Indicators A and B increased in Oct and Dec, Indicator C increased in Dec)

Indicator	Oct 2021	Nov 2021	Dec 2021
A	1	0	1
B	1	0	1
C	0	0	1
TOTAL	1	0	1

Desired Results: (Notice Total is summed)

Indicator	Oct 2021	Nov 2021	Dec 2021
A	1	0	1
B	1	0	1
C	0	0	1
TOTAL	2	0	3

Re: Sum Switch Values

smpa01 — Mon, 20 Dec 2021 19:45:18 GMT

dcrow5378 does this give you what you need

Measure = SUMX ( 'QM', VAR PriorMonthAmt = CALCULATE ( [Max FAP], PARALLELPERIOD ( 'QM'[Report Month], -1, MONTH ) ) VAR Compare = IF ( ISBLANK ( PriorMonthAmt ), BLANK (), PriorMonthAmt - [Max FAP] ) RETURN SWITCH ( TRUE (), Compare = 0, 0, Compare < 0, 1, Compare > 0, 0 ) )

I am gueesing QM is the table where you have the column Indicator

Re: Sum Switch Values

dcrow5378 — Mon, 20 Dec 2021 19:53:30 GMT

smpa01 Yes, QM is the table where I have the column indicator.
I did try something like this, and I just tried yours, and for some reason this is giving me all zeros, even though the values have increased from previous months?

Re: Sum Switch Values

smpa01 — Mon, 20 Dec 2021 20:02:15 GMT

dcrow5378 sorry 'bout that. Is it kindly possible to reproduce a pbix in a small scale with identical measures for me/anyoneelse to debug?

Re: Sum Switch Values

dcrow5378 — Mon, 20 Dec 2021 20:36:35 GMT

Here is a link to the .pbix
https://drive.google.com/file/d/1xpaAOVl7xWQclEXDcMcWZl8OUpCqW9X7/view?usp=sharing

Re: Sum Switch Values

smpa01 — Mon, 20 Dec 2021 20:54:03 GMT

dcrow5378 can you try this

Measure 2 = SUMX(addcolumns(summarize(qm,QM[FacilityID],QM[Indicator],QM[Report Month]),"mx",[Measure]),[mx])

Re: Sum Switch Values

dcrow5378 — Mon, 20 Dec 2021 21:08:32 GMT

smpa01 That seemed to do the trick! I would not have got that one. Appreciate the quick help here!