Weird interaction between measures

user_2319439 0
Viewed 30

I'm trying to create a measure which can give me the primo date value for a given timeframe eg. start date of: current month, current year, YTD etc, I'm using calculation groups, but i've boiled the problem down to this.
In this example i'm tying to find the value of the stat date of current YTD-period, which should be 01/01/2022 :

primotest = 
var primoDateValue = TOTALYTD(
                        FIRSTDATE( Dato[FuldDato])
                        , Dato[FuldDato]
                    )

//in my actual measure i have to filter the primoDateValue by [level2]-column, which i why it is important to include it in this example.
var randomMeasure = 
        CALCULATE(1
            ,'Dim funktionsbudgetter'[level2] = "this doesn't matter" 
        )

return 
primoDateValue

for some unknown and really weird reason the randomMeasure interacts with the primoDateValue measure.

if 'Dim funktionsbudgetter'[level2] = "this doesn't matter" is included in randomMeasure. the result is incorrect

enter image description here

if I remove ''Dim funktionsbudgetter'[level2] = "this doesn't matter" the value is correct

enter image description here

My datamodel looks like this:
enter image description here

I'm guessing that i have somekind of problem with my datamodel. but no matter what that problem is. I can't think of a single scenario that causes an interaction between primoDateValue and randomMeasure.
I'm hoping that some of you can think up a scenario that would cause the weird interaction between the measures. so that I can figure out where the problem actually is.

dax powerbi
1 Answers

okay so i figured out the problem or actually problems.
it turns out that it is a combination of using time intelligent functions eg. TotalYTD, calculation groups (CG) and composit models.

basically, you should be really careful when using CG and composit models. in short, you can't use remote data with a local defined CG and vice versa. it will straight up ignore the CG item and just return the normal measure.
you can read more about it here: sqlbi - guide

I also found that the time intelligence function doesn't always play nice with composit models. which i also the problem in my "boiled-down" measure in my OP. I don't know exactly why. it works in other scenarios.
for now i can just rewrite my measure to avoid TI functions

user_23194390
Related