Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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!

 

1 ACCEPTED SOLUTION
v-deddai1-msft
Community Support
Community Support

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
            )
)

 

Capture1.PNG

 

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

View solution in original post

5 REPLIES 5
v-deddai1-msft
Community Support
Community Support

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
            )
)

 

Capture1.PNG

 

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
Super User
Super User

@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. 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

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
Not applicable

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. 👍

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.