Forum Discussion

ZMalik1's avatar
ZMalik1
New Member
2 years ago
Solved

New Measure is removing the filter

Hi all, first time posting.

I have a filter running on my dataset which takes anything as current month and above as CY else PY and I've filtered to just CY.

I'm now calculating change in current week "CW" vs previous week "PW" but as that change is -100% because of both values being blank the filter has now been kicked out and it shows months I'm not interested in.

Any idea how I could correct please? If I filter asks change to not show -100% then I lose those values where there was a value in CW but not PW and vice versa.

 

Thank you for your help

Need Help
  • Alex87's avatar
    Alex87
    2 years ago

    Hello ZMalik1 ,

    the variables will hold the sum of your numeric colums. Based on your screen show I would say that

    VAR _CW = SUM('YourTableName[CW ASKs])

    VAR _PW =  SUM('YourTableName[PW ASKs])

5 Replies

  • Alex87's avatar
    Alex87
    Icon for Solution Sage rankSolution Sage
    2 years ago

    You need to put a condition to exclude the situation where both CW and PW are empty.
    Give this a try:

     

     

     

    Measure = 
VAR _CW = [CW]
VAR _PW = [PW]
VAR _Result = 
IF(
    ISBLANK(_CW) && ISBLANK(_PW), BLANK(), 
    DIVIDE((_CW -_PW), _PW )
)
RETURN
_Result

     

     

    If it answers your need, please mark the post as the solution. Thanks!

     

  • ZMalik1's avatar
    ZMalik1
    New Member
    2 years ago

    Hi Alex,
    Thank you very much for that, I get what you mean and you're absolutely right.

     

    Thanks for sharing the code, I seem to get a reference error -

     

    So I tried to replace "cw" with the actual column name which is cw asks and the same for pw but seems to still throw it out and doesnt pick up the column, you can what results it finds instead and also showing the second from the end 3rd and 2nd column that are the reference points as guidance -

     

    Thanks for your help!

  • Alex87's avatar
    Alex87
    Icon for Solution Sage rankSolution Sage
    2 years ago

    Hello ZMalik1 ,

    the variables will hold the sum of your numeric colums. Based on your screen show I would say that

    VAR _CW = SUM('YourTableName[CW ASKs])

    VAR _PW =  SUM('YourTableName[PW ASKs])

    • ZMalik1's avatar
      ZMalik1
      New Member
      2 years ago

      Worked - brilliant. Thank you!

       

      Am I right in thinking the VAR _CW is where you're denoting the variable and getting a short form of it which is what is calculated out later in the code. (Interesting to see that approach)

      Thanks again!

    • ZMalik1's avatar
      ZMalik1
      New Member
      2 years ago

      Ah right - I see, thank you!

       

      Let me give that a try. Appreciate it. ðŸ™‚