Row Context not removing filter in IF Statement

[Anonymous]

Hi,

I'm hoping someone can help.

I'm trying to undestand why my SUMX formula appears to not be taking into account the ALL to remove the filter on the months (I need it take the volume across the full 12 months rather than the invdividual month's volume).  In theory all values should come out as 0 but instead it appears to be passing the IF statement.

If I remove the SUMX the formula appears to be working fine.

DAX Formula:

SUMX('PBCS Pull',

        IF(

             Calculate(

                    SUM('PBCS Pull'[.Prev Volume]),

                    ALL('PBCS Pull'[Month])

             ) = 0,

            ([Curr Rev/Vol]-[Prev Rev/Volume])* 'PBCS Pull'[.Curr Volume],

            0

        )

)

Below is the data Set in Excel

Product	Month	.Prev Volume	.Prev Rev	.Curr Volume	.Curr Rev	.Prev Rev/Volume	.Curr Rev/Volume
Product 1	Jan	0.00	0.00	2.16	17.32	-	8.02
Product 1	Feb	0.00	0.00	2.69	15.46	-	5.74
Product 1	Mar	0.00	0.00	4.45	27.12	-	6.09
Product 1	Apr	0.00	0.00	0.28	1.78	-	6.27
Product 1	May	0.00	0.00	0.28	1.71	-	6.11
Product 1	Jun	0.00	0.00	0.45	2.77	-	6.17
Product 1	Jul	0.00	0.00	0.74	4.39	-	5.95
Product 1	Aug	0.00	0.00	0.03	0.15	-	5.94
Product 1	Sep	0.00	0.00	0.03	0.16	-	5.94
Product 1	Oct	5.40	38.89	0.45	2.39	7.20	5.36
Product 1	Nov	5.13	53.54	0.37	1.96	10.44	5.31
Product 1	Dec	1.62	10.07	0.28	1.50	6.21	5.41

Thanks in advance

[v-deddai1-msft · Accepted solution]

Hi @Anonymous ,

According to your expected output, would you please use the following calculated column:

Column =

SUMX (

    'PBCS Pull',

    IF (

        CALCULATE ( SUM ( 'PBCS Pull'[Prev Volume] ), ALLEXCECT ( 'PBCS Pull', 'PBCS Pull'[Prouduct] ) ) = 0,

       'PBCS Pull'[Curr Rev],

        0

    )

)

Best Regards,

Dedmon Dai

[Greg_Deckler]

Probably because you do not have an ALL in your outer SUMX. That is super bizarre formula structure by the way, extremely weird.

[Anonymous]

Sorry I'm not sure I follow.

I'm fairly new to power BI so bare with my on the questions and the poor formula structure.

Any feedback on best practices are welcomed.
Thanks

[v-deddai1-msft]

Hi @Anonymous ,

Please try the following dax:

Column =

SUMX ('PBCS Pull',

IF (

CALCULATE ( SUM ( 'PBCS Pull'[Prev Volume] ), ALL ( 'PBCS Pull' ) ) = 0,

( 'PBCS Pull'[Curr Rev/Volume] - 'PBCS Pull'[Prev Rev/Volume] )

* 'PBCS Pull'[Prev Volume],

0

)

)

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Anonymous]

Hi Dedmon,

The issue is that change in the ALL criteria should be month specific.  In theory it should be passing the if statement if the "Prev Volume" SUM from Jan to Dec = 0 for the specific product.

If another product is added without Prev Volume it should pass the IF statement as true.  Where as the suggested formula makes everything 0.

Please see below with the ideal output.

Product	Month	.Prev Volume	.Prev Rev	.Curr Volume	.Curr Rev	.Prev Rev/Volume	.Curr Rev/Volume		Expected Output
Product 1	Jan	-	-	2.2	17.3	-	8.0		0.0
Product 1	Feb	-	-	2.7	15.5	-	5.7		0.0
Product 1	Mar	-	-	4.5	27.1	-	6.1		0.0
Product 1	Apr	-	-	0.3	1.8	-	6.3		0.0
Product 1	May	-	-	0.3	1.7	-	6.1		0.0
Product 1	Jun	-	-	0.4	2.8	-	6.2		0.0
Product 1	Jul	-	-	0.7	4.4	-	6.0		0.0
Product 1	Aug	-	-	0.0	0.2	-	5.9		0.0
Product 1	Sep	-	-	0.0	0.2	-	5.9		0.0
Product 1	Oct	5.4	38.9	0.4	2.4	7.2	5.4		0.0
Product 1	Nov	5.1	53.5	0.4	2.0	10.4	5.3		0.0
Product 1	Dec	1.6	10.1	0.3	1.5	6.2	5.4		0.0
Product 2	Jan	-	-	34.3	248.8	-	7.3		248.8
Product 2	Feb	-	-	65.2	502.9	-	7.7		502.9
Product 2	Mar	-	-	77.6	558.6	-	7.2		558.6
Product 2	Apr	-	-	50.6	377.6	-	7.5		377.6
Product 2	May	-	-	71.8	539.9	-	7.5		539.9
Product 2	Jun	-	-	59.2	446.8	-	7.5		446.8
Product 2	Jul	-	-	50.9	334.2	-	6.6		334.2
Product 2	Aug	-	-	45.1	309.6	-	6.9		309.6
Product 2	Sep	-	-	43.1	304.3	-	7.1		304.3
Product 2	Oct	-	-	38.1	268.4	-	7.0		268.4
Product 2	Nov	-	-	36.8	270.0	-	7.3		270.0
Product 2	Dec	-	-	45.6	324.4	-	7.1		324.4

Let me know your thoughts.

Thanks,

Alex 

[v-deddai1-msft · Accepted solution]

Hi @Anonymous ,

According to your expected output, would you please use the following calculated column:

Column =

SUMX (

    'PBCS Pull',

    IF (

        CALCULATE ( SUM ( 'PBCS Pull'[Prev Volume] ), ALLEXCECT ( 'PBCS Pull', 'PBCS Pull'[Prouduct] ) ) = 0,

       'PBCS Pull'[Curr Rev],

        0

    )

)

Best Regards,

Dedmon Dai

[Anonymous]

Thanks Dedmon this worked.  Is there a reason why ALL('PBCS Pull'[Month]) doesn't work?

Thanks,

Alex 

[MarcoPessina]

The reason why ALL('PBCS Pull'[Month]) doesn't work is because of the evaluation context of your IF function. Both if you use your formula in a calculated column and in a measure, the evaluation context comes from the original row context:

• Calculated column: row context based on the current row. It includes all the columns
• Measure: row context created by SUMX that iterates the rows. It includes all the columns

In both cases the row context is transformed in a filter context by the use of CALCULATE according to the context transition rule.

At this point, IF...SUM is in an evaluation context filtered by all the columns. If you use ALL('PBCS Pull'[Month]) you remove the filter only by Month column. What you need is to remove all filters but Product and that's why you have to use ALLEXCEPT(Product).

Hope it helps 🙂. 

[Anonymous]

Hi Marco,

Thank you for the explaination.  Just to ensure I'm reading this correctly does this mean that measures technically create evaluation context to iterate through based on ALL rows in the given table?  Ie. when it iterates through the first row it is technically filtering the following:
Product: Product 1

Month: Jan

.Prev Volume: 0

.Prev Rev: 0

.Curr Volume: 2.2

.Curr Rev: 17.3

etc...

Thus as an example even though I've removed all filters on months its still filtering for "2.2" only in .Curr Volume Column (given Jan only has 2.2 even though the month filter is removed Jan is still the only month applicable in the output from calculate)?  I assume the above still holds even if my visualization only includes the "Product" & "Month" column given the SUMX is referencing off of the full PBCS Pull Table?

I'm still new to Power BI so really appreciate the help everyone has given.

Thanks,

Alex 

[MarcoPessina · Accepted solution]

Hi @Anonymous ,

I would suggest to have a look a this article to start: https://www.microsoftpressstore.com/articles/article.aspx?p=2449191.

DAX experts are way better than me to explain things. Anyway, to the best of my knoledge:

Question: Just to ensure I'm reading this correctly does this mean that measures technically create evaluation context to iterate through based on ALL rows in the given table?

Answer: I would't say that "measures technically create evaluation context". The evaluation context is created by: filter context (row/column selection, slicers, filter selection) and row context (calculated columns, row iteration functions).

Question: Thus as an example even though I've removed all filters on months its still filtering for "2.2" only in .Curr Volume Column (given Jan only has 2.2 even though the month filter is removed Jan is still the only month applicable in the output from calculate)? 

Answer: I would say that in your example all the columns are filtering unless you explicitely remove/modify the filter (eg: ALL, ALLEXCEPT)

Question: I assume the above still holds even if my visualization only includes the "Product" & "Month" column given the SUMX is referencing off of the full PBCS Pull Table?

Answer: That's correct

Hope it helps.

Cheers,

Marco