Forum Discussion
Create a static variable based on an expression for a dax measure
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!
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
5 Replies
Anonymous not really clear what you are trying to achieve? What is your business logic?
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 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.
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
