DAX Formula using TreatAS contains a BUG

[sai1995]

I have a modell which use in DAX Formula TreatAs. However it computes wrong results. I have made a comparison with userelationship. That is working well.
It seems that TreatAs is not evaluating 2 records out of 5.7 Mio.

Please see the Report Tab Error Error "UseRelationship vs TreatAS".
The measure Bilanz is using UseRelationship and works correct
The measure Bilanz TreatAS is using TreatAS and not shows correct Data.

Both measures should display the same amount. It works well for all Accounts except of Account "15400 Baumaschinen". There it seems that TreatAs is not see 2 Records in Table Buchungen. (See Screenshot).

Can you please Debut the DAX Formula engine and see why Bilanz TreatAS is not showing the correct result?

[miraglia]

After reviewing the DAX Code and the Data the following code resolved the Problem:

Bilanz =

CALCULATE(

    SUM('Buchungen'[Betrag]),

    FILTER(

        ALL('Zeitachse'),

        ISONORAFTER('Zeitachse'[Datum], MAX('Zeitachse'[Datum]), DESC)

    ), Kontenplan[GuV_Bilanz] =1, ALL(Kostenstellen)

,TREATAS(VALUES(Mandanten[Konzernmandant]), Buchungen[Konzernmandant]))

There was a relationship between Buchungen to Kostenstellen to Mandanten which filterd 2 records out of 5.7 Mio records because the Kostenstelle in Buchungen was not a valid one.....

[Anonymous]

As I said, it's roughly the lack of knowledge about how DAX works that was at fault, not DAX itself. Many people I've been in touch with have claimed that DAX does not work correctly or that it has bugs :))) But the story is always the same. Glad you've been able to squash "the bug" 🙂

[sai1995]

Hi @Anonymous please answer for above question raised by @miraglia 

[Anonymous]

What screenshot, errors and tabs are you talking about? There's nothing like that in here... By the way, as is usual with DAX, I'm 100% sure it's your misunderstanding of what DAX is doing is at fault here and not DAX itself.

[miraglia]

One DAX Measure is:

Bilanz =

CALCULATE(

    SUM('Buchungen'[Betrag]),

    FILTER(

        ALL('Zeitachse'),

        ISONORAFTER('Zeitachse'[Datum], MAX('Zeitachse'[Datum]), DESC)

    ), Kontenplan[GuV_Bilanz] =1

,USERELATIONSHIP(Buchungen[Konzernmandant], Mandanten[Konzernmandant] ))

and the other is:

Bilanz TreatAS =

CALCULATE(

    SUM('Buchungen'[Betrag]),

    FILTER(

        ALL('Zeitachse'),

        ISONORAFTER('Zeitachse'[Datum], MAX('Zeitachse'[Datum]), DESC)

    ), Kontenplan[GuV_Bilanz] =1

,TREATAS(VALUES(Mandanten[Konzernmandant]), Buchungen[Konzernmandant]))

They are showing different Results, but according to the Microsoft Documentation both Measures should produce the same result.

[Anonymous]

Here's a simple example to show you that your measures do not necessarily have to be logically equivalent:

Count With Relationship = COUNTROWS( 'Fact' )

Count With Treatas = 
    CALCULATE(
        COUNTROWS( 'Fact' ),
        CROSSFILTER( 'Fact'[Item], Items[Item], None ),
        TREATAS(
            VALUES( Items[Item] ),
            'Fact'[Item]
        )
    )

[Anonymous]

To be able to say if anything is really wrong with DAX I need data that demonstrates the issue. Without the data I can't say anything but what I've already said. One thing to note immediately is the fact that in the first formula you are making a connection active (and probably deactivate another one) and in the other no such thing takes place. That means you've got two different situations. I would have to analyze the interplay among all the pieces that make up the code.