Currency functionality using "Calculation groups" doesn’t work in measure of second fact table

[marcstoffels]

Who can help me to have this Currency implementation implemented successfully, using "Calculation groups"?

In an "Analysis Services" tabular cube (model 1500) I have implemented currency functionality using "Calculation groups", as explained in:
https://www.sqlbi.com/articles/currency-conversion-in-power-bi-reports/#

This works fine for the measures in 1 fact table, but not in a second fact table, that has measures on top of the measures from the first fact table.

I reproduced this issue in a Power BI report with an Excel datasource.

The model, having 2 fact tables:

Via Tabular Editor, I added the calculation group:

The expression of ‘Report Currency’:

VAR MeasureName =

    SELECTEDMEASURENAME ()

VAR SkipConversion =

    NOT ISCROSSFILTERED ( 'Currency' ) -- TODO: implement the conditions for which the calculation should NOT be applied   

        || (

                   SEARCH ( "LandedPrice", MeasureName, 1, 0 ) = 0

                && SEARCH ( "LP-pc", MeasureName, 1, 0 ) = 0

                && SEARCH ( "Marc-pc", MeasureName, 1, 0 ) = 0

           )

RETURN

    IF (

        SkipConversion,

        SELECTEDMEASURE (),

        VAR SelectedCurrency =

            SELECTEDVALUE ( 'Currency'[Code] )

        VAR Result =

            IF (

                SelectedCurrency = "EUR",

                SELECTEDMEASURE (),

                IF (

                    SEARCH ( "LandedPrice", MeasureName, 1, 0 ) > 0,

                    AVERAGEX ( ExchangeRate, SELECTEDMEASURE () * 'ExchangeRate'[Rate] ),

                    SUMX ( ExchangeRate, SELECTEDMEASURE () * 'ExchangeRate'[Rate] )

                )

            )

        RETURN

            Result

    )

The first visual will show the measure in the first fact table (Parts):

PartsCosts_LP-pc14 = SUMX(Parts, [PartCount] * [LandedPrice-col])

in the currency EUR:

When switch to USD (note that the currency rate is equal to 2, because I had an issue with the decimals in the source dataset):

So the calculation group, uses the exchange rate to multiply:   2,787.82 * 2 = 5,575.64 -> this is correct !!
Note that you could hide the “Conversion Calculation” column, by using it in a filter and hide that filter.

In the second visual I have created a new measure in the second fact table (Configurations):

Marc-pc24a =

DIVIDE(

    Parts[PartsCosts_LP-pc14],

    SUM(Configurations[IsActive])

)

In EUR this will be: 2,787.82 / 645 = 4.32 EUR

When changing the slicer to ‘USD’, the calculation is executed, but it only does the Parts[PartsCosts_LP-pc14] part.
So the currency calculation returns the result of the first parameter in the DIVIDE operation, but doesn’t execute the DIVIDE operation:
DIVIDE(

    Parts[PartsCosts_LP-pc14],

    SUM(Configurations[IsActive])

)

How can we explain this behavior?

Inspired by this link:

http://reader.epubee.com/books/mobile/03/03d876188bcd39b0b307ecb6ebb63aee/text00016.html

There is nothing magical about calculation groups: They are tables, and as such they can be filtered by CALCULATE like any other table.  When CALCULATE applies a filter to a calculation item, DAX uses the definition of the calculation item to rewrite the expression before evaluating it.

So, I have created a new measure, in which I have implicit defined, that the currency conversion must be executed, but then on the “highest” level, so within the second fact table measure and then after the DIVIDE:

Marc-24b =

CALCULATE(

    DIVIDE(

        Parts[PartsCosts_LP-pc14],

        SUM(Configurations[IsActive]) --[DIFS-c0.07]

    ),

    'Currency Conversion'[Conversion Calculation] = "Report Currency"

) 

This executes the conversion at the “highest” level, and shows the correct value : 8.64 USD

But this then needs to implement this on each measure…
So no solution, but this what we want related to the execution sequence.

Also then the currency format expression is not executed.

Note that I have the format expression implemented within the calculation group in the following way:

Who has experience with a simular implementation?

[daxer-almighty]

Hi @marcstoffels 

What you don't know about calculation groups, though, is that they only affect measures, not expressions. So, this would work as you'd expect: calculate( [measure], CalcGroup[CalcItem] = "CalcItem" ) but this would not: calculate( sum( T[Col] ), CalcGroup[CalcItem] = "CalcItem" ). The hard and unbreakable rule is that filtering with calculation groups should only ever be applied to single measures and there should be no exception to it if you want to stay on the safe side and know what you're calculating. On top of that, caclulation groups have priorities that should be set in a correct way (which sometimes might be hard). And one last thing... The application of calculation groups (meaning the re-write of a measure) and the execution of code are in reverse order. Please go to www.sqlbi.com and find the multi-part article about calculation groups. You'll learn A LOT about how they work and it'll let you avoid the common pitfalls.

[marcstoffels]

Hi,

Thanks you for your response.

I know it works only on measures, as by design: https://github.com/otykier/TabularEditor/issues/636
But I guess in my situation the issue is that I combine two measure out of two different fact tables.

So when I define a new measure within only the first fact table (Parts), then the DIVIDE will be executed.

As a test my measure, that divides the parts costs by the number of parts, so this will show the price again:

Marc-pc99 =

DIVIDE(

   [PartsCosts_LP-pc14],

   SUM([PartCount])

)



Regards,
Marc.

[DataZoe]

@marcstoffels Have you tried making SUM(Configurations[IsActive])  an explicit measure, and then using that in your divide?

DIVIDE(

    Parts[PartsCosts_LP-pc14],

    Parts[IsActiveSum]

)

That may have been what you did with the --[DIFS-c0.07] but I wasn't sure. 

Another thought would be to move the IF statement in your calculation group to a VAR and return that VAR, or try to remove the VARs in it, making one long DAX. 

These are the things I would try, but this one is a complex solution. 

[marcstoffels]

Hi DataZoe,

Thank you for your response to my issue.
I indeed tried to have the SUM of configurations first in a seperate measure (DIFS-c.007), or have it as a VAR in the message itself, but this didn't solve it.

Regards,
Marc.