what's the difference between the two filters

[Jeanxyz]

I'm struggling why the two measures generate different results. According to me, they should give the same result as I only have one currency selected.

Company Currency Amount Base_PL_CP3 =

var DateSelection= FILTER

('Dim_Date',

'Dim_Date'[YearMonth]

= [LastSelectedYearMonth])

var CompCurrAmt = CALCULATE(sum('Fact_GL Entries'[Company Currency Amount]),filter('Dim_Group GL Account','Dim_Group GL Account'[Level 0]="PL"))

var result1=calculate(CompCurrAmt

,DateSelection

)

var result2 = DIVIDE(result1,[SelectedUOM])

return

result2

**********************************************************************************************************

Company Currency Amount_PL_CP2 =

VAR DatesSelection =

FILTER

('Dim_Date',

'Dim_Date'[YearMonth]

= [LastSelectedYearMonth])

VAR BS =

FILTER(VALUES('Dim_Group GL Account'[Level 0]),'Dim_Group GL Account'[Level 0] = "PL" )

var result1 =

CALCULATE(

SUMX (

VALUES('Fact_GL Entries'[Company Currency]),

SUM ( 'Fact_GL Entries'[Company Currency Amount] )

),DatesSelection,BS)

var result2 = DIVIDE(result1,[SelectedUOM])

return

result2

[Jeanxyz]

I finally figured out the reason, in measure CP3, there is a bug in the expression below

var result1=calculate(CompCurrAmt

,DateSelection

)

 As CompCurrAmt is the output of previous step (a var expresison), the expression is stored as a fixed value, hence the DateSelection filter won't work as expected on a fixed value.

[selimovd]

Hey @Jeanxyz ,

it's a little difficult to imagine how the files work without any information.

Maybe you can provide a file? That would make it easier to understand the measure.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Jeanxyz]

Sorry, this a complex model, hence I cannot easily provide a sample file. It seems to me the only difference between the two measures are:  CP3 sums up'Fact_GL Entries'[Company Currency Amount] and apply PL filter and dateselection filter afterwards; CP2 sums up Fact_GL Entries'[Company Currency Amount] via a sumx formula and apply PL filter (via var BS) and dataselection filter afterwards.

I don't think sumx() will differ from sum as they are summing up the same column, the two filters should work the same way as they are wrapped by calculate(), but the results I get are different.