Create a static variable based on an expression for a dax measure

[Anonymous]

Hi,

I'm having difficulty figuring out how to create a static variable within a dax measure.

With a normal programming language, i'm use to variables being static at instantiation. However, with dax it seems to be in the context of the data it's pulling.

The weirder thing is when I DEFINE in DAX Studio, it instantiates the variable as static and my subsequent EVALUATE expression works as I would like it to. However, Power BI is different (presumabely because of the filter context at runtime?)

For an example, see below:

DATA:

Rank	Day Of Week	Sales
22	Monday	$5
22	Tuesday	$7
22	Wednesday	$8
22	Thursday	$8
22	Friday	$6
22	Saturday	$9
22	Sunday	$3
23	Monday	$0
23	Tuesday	$0
23	Wednesday	$0

DAX:

Week Over Week Test =

VAR max_business_week =CALCULATE ( MAX ( PowerBI_Speed_Test[Rank] )  )

RETURN
    CALCULATE (
        SUM ( PowerBI_Speed_Test[Sales] ),
        FILTER (
            PowerBI_Speed_Test,
            PowerBI_Speed_Test[Rank] = max_business_week
        )
    )

What I expect to be returned:

23	Monday	$0
23	Tuesday	$0
23	Wednesday	$0
23	Thursday	$0
23	Friday	$0
23	Saturday	$0
23	Sunday	$0

What is actually returned:

22	Thursday	$10
22	Friday	$4
22	Saturday	$7
22	Sunday	$8
23	Monday	$0
23	Tuesday	$0
23	Wednesday	$0

Thanks in advance!

[v-deddai1-msft · Accepted solution]

Hi @Anonymous ,

You can use lookupvalue:

table2 =
ADDCOLUMNS (
    CROSSJOIN ( VALUES ( 'Table'[Rank] ), VALUES ( 'Table'[Day Of Week] ) ),
    "sales",
        VAR a =
            LOOKUPVALUE (
                'Table'[Sales],
                'Table'[Rank], 'Table'[Rank],
                'Table'[Day Of Week], 'Table'[Day Of Week]
            )
        RETURN
            IF (
                ISBLANK ( a ),
                LOOKUPVALUE (
                    'Table'[Sales],
                    'Table'[Rank], 'Table'[Rank] - 1,
                    'Table'[Day Of Week], 'Table'[Day Of Week]
                ),
                a
            )
)

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[parry2k]

@Anonymous so to clarify, if a day of the week doesn't have a transaction (or sales) then take a sale from the same day from the previous week. Is that what you are looking for or if no sales found for a day of the week, make it zero. 

[parry2k]

@Anonymous not really clear what you are trying to achieve? What is your business logic?

[Anonymous]

Hi Parry,

So it's a bit of a weird scenario. I'll save you the specifics.

But the gist of it is that we have a process that updates data in the DB every week. The days are modular so sometimes the update fails because it's collecting data from multiple sources (the retail POS that populates the database doesn't work, or there's no power etc).

The goal of getting zeros would be to create logic that is IF Sales = 0 then notify the user of the failed job and show the previous weeks data. Something to that effect.

It's something I've never had to do before and probably won't have to again (hopefully)

[Anonymous]

Hi,

I just figured out a work around. Instead of using MAX, I used LOOKUPVALUE.

Max is definitely more performant but Lookupvalue returns a static lookup (I believe).

It's a bit slower but it's intuitive and easy for others to understand. 👍