topic Re: ALL Filter is causing column subtotal value to be incorrect in DAX Commands and Tips

ALL Filter is causing column subtotal value to be incorrect

powerbi-help1 — Tue, 08 Jun 2021 11:57:45 GMT

The following formula is pulling the correct value in the matrix but the column subtotal of the row is incorrect and only totaling the last value in the matrix row.

CALCULATE([Gross_Amount], FILTER(ALL(Periods), Periods[Index] = MAX(Periods[Index])-13))

The intentioion of the formula is have the current year period column headings ignored and then pull the previous year figures for the same period of time. IE P05 2021 ignored, data for P05 2020 pulled instead.

Re: ALL Filter is causing column subtotal value to be incorrect

Anonymous — Thu, 10 Jun 2021 08:43:54 GMT

[Measure] = var VisiblePeriods = DISTINCT( Periods[Index] ) var PeriodsBack = FILTER( ALL( Periods[Index] ), ( Periods[Index] + 13 ) in VisiblePeriods ) var Result = CALCULATE( [Gross_Amount], PeriodsBack, // Depending on what you really want // your measure to do, you can include // this line below or exclude it: ALL( Periods ) ) return Result

Re: ALL Filter is causing column subtotal value to be incorrect

powerbi-help1 — Tue, 08 Jun 2021 21:43:34 GMT

Thank you for the response. The formula did not work and returned a 0 value (after being edited to not have an error).

When I tried to use the result portion above as written and removed the //lines keeping the ALL (Periods) there was an error:

"The syntax for 'ALL' is incorrect. (DAX(var VisiblePeriods = DISTINCT( Periods[Index] )var PeriodsBack = FILTER( ALL( Periods[Index] ), ( Periods[Index] + 13 ) in VisiblePeriods )var Result = CALCULATE( [Gross_Amount], PeriodsBack ALL( Periods ) )return Result))."

Formula written as that produced error:

var VisiblePeriods = DISTINCT( Periods[Index] )

var PeriodsBack =

FILTER(

ALL( Periods[Index] ),

( Periods[Index] + 13 ) in VisiblePeriods

)

var Result =

CALCULATE(

[Gross_Amount],

PeriodsBack

ALL( Periods )

)

return

Result

Essentially I need the matrix to ignore the Year-Period columns and index from the CY period (ie P05 2021) to PY (P05 2020, hence the -13 on the index formula). My original formula I sent pulls each period but does not subtotal properly.

Thank you.

Re: ALL Filter is causing column subtotal value to be incorrect

powerbi-help1 — Wed, 09 Jun 2021 21:15:45 GMT

I have made some additional changes to the formula, please see below.

var VisiblePeriods = DISTINCT( Periods[Index] )
var PeriodsBack =
FILTER(ALL(Periods),MAX(Periods[Index])-13 in VisiblePeriods)
var Result =
CALCULATE([Gross_Amount],PeriodsBack)
return
Result

This returns a zero value but no error, any advice on how to solve?

Thank you.

Re: ALL Filter is causing column subtotal value to be incorrect

Anonymous — Thu, 10 Jun 2021 08:43:35 GMT

There was a missing comma after PeriodsBack. I've corrected it above. Apart from this the formula works correctly when the model is set up the way I think it is. Since you have not shown the model... I had to imagine what it is. I can easily demonstrate that it does what it should.

Re: ALL Filter is causing column subtotal value to be incorrect

powerbi-help1 — Thu, 10 Jun 2021 21:15:44 GMT

Thank you, yes i was able utilize the formula to to produce the results as needed.

Re: ALL Filter is causing column subtotal value to be incorrect

Anonymous — Thu, 10 Jun 2021 21:27:57 GMT

Great 🙂