Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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...
Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    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's avatar
parry2k
Super User
5 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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)