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 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] ) )
)
)
)
Solved! Go to 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.
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.
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |