Smarter way of handling if/cases in DAX?

[cplesner]

I have a little calculation that is used to calculate the difference in sales between a customer and product selected vs a reference customer and product selected. For this i use the below formula. Now this is ok when there are 3 scenarioes  (customer selected, product selected), (customer selected, no product selected) or (no customer selected, product selected). But if i need to introduce another refence like time, there are suddenly 9 scenarioes, so i'm looking for a smarter way - feedback is welcome 🙂

Total Net Amount (Reference) =
IF (
    ISCROSSFILTERED ( 'Reference Product' ) = TRUE
        && ISCROSSFILTERED ( 'Reference Customer' ) = TRUE,
    CALCULATE (
        SUM ( 'Invoice Lines'[TotalNetAmountDefaultCurrency] ),
        ALL ( 'Product' ),
        USERELATIONSHIP ( 'Invoice Lines'[Product Number], 'Reference Product'[Product Number (Reference)] ),
        ALL ( 'Customer' ),
        USERELATIONSHIP ( Invoice[Debitor Number], 'Reference Customer'[Customer Number (Reference)] )
    ),
    IF (
        ISCROSSFILTERED ( 'Reference Product' ) = TRUE
            && ISCROSSFILTERED ( 'Reference Customer' ) = FALSE,
        CALCULATE (
            SUM ( 'Invoice Lines'[TotalNetAmountDefaultCurrency] ),
            ALL ( 'Product' ),
            USERELATIONSHIP ( 'Invoice Lines'[Product Number], 'Reference Product'[Product Number (Reference)] )
        ),
        IF (
            ISCROSSFILTERED ( 'Reference Product' ) = FALSE
                && ISCROSSFILTERED ( 'Reference Customer' ) = TRUE,
            CALCULATE (
                SUM ( 'Invoice Lines'[TotalNetAmountDefaultCurrency] ),
                ALL ( 'Customer' ),
                USERELATIONSHIP ( Invoice[Debitor Number], 'Reference Customer'[Customer Number (Reference)] )
            ),
            CALCULATE ( SUM ( 'Invoice Lines'[TotalNetAmountDefaultCurrency] ) )
        )
    )
) 

[Phil_Seamark · Accepted solution]

Hi @cplesner

I prefer to use the SWITCH function in place of nested IF statements

You could do something like this.  

Total Net Amount (Reference) = 
SWITCH(
	TRUE(),
		-- WHEN 
		ISCROSSFILTERED ( 'Reference Product' ) = TRUE && ISCROSSFILTERED ( 'Reference Customer' ) = TRUE ,
		-- THEN --
			[New Measure 1] ,
		-- WHEN 
		ISCROSSFILTERED ( 'Reference Product' ) = TRUE && ISCROSSFILTERED ( 'Reference Customer' ) = FALSE,
		-- THEN --
			[New Measure 2],
		-- WHEN 			
		ISCROSSFILTERED ( 'Reference Product' ) = FALSE && ISCROSSFILTERED ( 'Reference Customer' ) = TRUE ,
		-- THEN --
			[New Measure 3] ,
		-- ELSE --
		SUM ( 'Invoice Lines'[TotalNetAmountDefaultCurrency] )
)

And I would create measures to separate out the logic and make it more readable.  So where I have [New Measure 1], that new measure could be the same logic you previously had at that step.