Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

If statement doesn't allow to select columns

Hello guys,    I hope you are well.    I have a very simple problem but I wasn't able to fix it.    Indeed, I would like to create a IF statement  like this = IF (And([QT received]=0,[Status]="...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous,

    What the 'blank' means? 'blank' text or null value? If you mean the null value, you can use camargos88 's suggestion. For 'blank' text, you need to change the conditions to compare with 'blank'.

    Measure =
VAR qtReceiver =
    SELECTEDVALUE ( 'Table'[QT received] )
VAR _status =
    SELECTEDVALUE ( 'Table'[Status] )
RETURN
    IF ( qtReceiver = "blank" && _status = "Closed", 1, 0 )

    BTW, the above formula not suitable to use to compare with summarize row contents. For this scenario, you can try to use the following measure formula:

    Measure =
VAR summary =
    SUMMARIZE (
        Table,
        [Name],
        [QT Receive],
        [Status],
        "If statement", IF ( [QT Receive] = "Blank" && _status = "Closed", 1, 0 )
    )
RETURN
    CONCATENATEX ( summary, [If statement], "," )

    Regards,

    Xiaoxin Sheng

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

Within a measure, you need to wrap a column reference in an aggregator like SUM, COUNT, MAX, MIN, AVERAGE, etc.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Thank you for you quick ansawer Greg_Deckler  ! 

 

I see but in my case, I don't know how to aggregate.

Indeed I have the data below and I would like to measure IF QT received is Blank and Status closed then 0 otherwise 1.

 

NameQT receivedStatusIf statement 
XblankClosed0
Y1Open1
Z0Open1
  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    camargos88 's formula should work. I generally just use MAX instead of SELECTEDVALUE but same kind of thing.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous,

    What the 'blank' means? 'blank' text or null value? If you mean the null value, you can use camargos88 's suggestion. For 'blank' text, you need to change the conditions to compare with 'blank'.

    Measure =
VAR qtReceiver =
    SELECTEDVALUE ( 'Table'[QT received] )
VAR _status =
    SELECTEDVALUE ( 'Table'[Status] )
RETURN
    IF ( qtReceiver = "blank" && _status = "Closed", 1, 0 )

    BTW, the above formula not suitable to use to compare with summarize row contents. For this scenario, you can try to use the following measure formula:

    Measure =
VAR summary =
    SUMMARIZE (
        Table,
        [Name],
        [QT Receive],
        [Status],
        "If statement", IF ( [QT Receive] = "Blank" && _status = "Closed", 1, 0 )
    )
RETURN
    CONCATENATEX ( summary, [If statement], "," )

    Regards,

    Xiaoxin Sheng

    • riteshsen's avatar
      riteshsen
      Regular Visitor
      1 year ago

      This worked! Thanks!...

  • camargos88's avatar
    camargos88
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Anonymous ,

    Try creating this measure:

     

    Measure =
    VAR _qtReceived = SELECTEDVALUE('Table'[QT received])
    VAR _Status = SELECTEDVALUE('Table'[Status])

    RETURN IF(_qtReceived = BLANK() && _Status = "Closed"; 0; 1)
     
    Ricardo