Forum Discussion

Ibaz's avatar
Ibaz
New Member
2 years ago
Solved

Multiple If AND ELSE querry

Hi All

I have two colums status and date - depend on the status and if there is a date of not i need to show the Result

 

HELP! every variation of IF AND i try in a calculated column gives a Syntax error 

 

Status Certification Expiration DateResult
Expired  Expired 
Expired                 date Expired 
Not started                dateCompleted
Not started Not started
In progress In progress
In progress                 dateIn progress
CompleteddateCompleted
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Ibaz ,

    Here some steps that I want to share, you can check them if they suitable for your requirement.

    Here is my test data:


    2.Create a measure 

     

     

    Measure = 
SWITCH(
    TRUE(),
    SELECTEDVALUE('Table'[Status]) = "Expired", "Expired",
    SELECTEDVALUE('Table'[Status]) = "In progress", "In progress",
    SELECTEDVALUE('Table'[Status]) =  "Completed", "Completed",
    SELECTEDVALUE('Table'[Status]) = "Not started" && SELECTEDVALUE('Table'[Certification Expiration Date]) = "date","Completed",
    SELECTEDVALUE('Table'[Status]) = "Not started","Not started"
)

     

     

    3. You can also create a column

     

    Result1 = 
IF (
     'Table'[Status] = "In progress",
    "In progress",
    IF (
         'Table'[Status] = "Expired",
        "Expired",
        IF (
             'Table'[Status]  = "Completed",
            "Completed",
            IF (
                'Table'[Status] = "Not started"
                    && 'Table'[Certification Expiration Date]  <> BLANK (),
                "Completed",
                "Not started"
            )
        )
    )
)

     


    3.Final output

     

     

    Best regards

    Albert He

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
     

4 Replies

  • ValtteriN's avatar
    ValtteriN
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Hi,

    Here is an example on how to do this using a filter measure:

    Measure 13 = IF(MAX('Table (20)'[Result])="Expired" && MAX('Table (20)'[Certification Expiration Date])<>BLANK(),1,0)


    Now place it in your visual:

     

    This way only the expired rows with date are shown.

    I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

    My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

    • Ibaz's avatar
      Ibaz
      New Member
      2 years ago

      Thanks - so would i create one filter for each of the seven combinations? 

      • ValtteriN's avatar
        ValtteriN
        Icon for Community Champion rankCommunity Champion
        2 years ago

        Hi,

        You can combine conditions in IF using "&&" Measure 13 = IF(MAX('Table (20)'[Result])="Expired" && MAX('Table (20)'[Certification Expiration Date])<>BLANK(),1,0)

        If you only want to show results with date you can drop part of the measure like this:

        Measure 13 = IF(MAX('Table (20)'[Certification Expiration Date])<>BLANK(),1,0)

        The base idea remains the same. Try to create IF function which fullfills the conditions you have. I.E. try to think what kind of logic determines te rows you want to show. IF(Condition1 = true && condition2 = true ...,1 (then 1),0 (else 0))

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Ibaz ,

    Here some steps that I want to share, you can check them if they suitable for your requirement.

    Here is my test data:


    2.Create a measure 

     

     

    Measure = 
SWITCH(
    TRUE(),
    SELECTEDVALUE('Table'[Status]) = "Expired", "Expired",
    SELECTEDVALUE('Table'[Status]) = "In progress", "In progress",
    SELECTEDVALUE('Table'[Status]) =  "Completed", "Completed",
    SELECTEDVALUE('Table'[Status]) = "Not started" && SELECTEDVALUE('Table'[Certification Expiration Date]) = "date","Completed",
    SELECTEDVALUE('Table'[Status]) = "Not started","Not started"
)

     

     

    3. You can also create a column

     

    Result1 = 
IF (
     'Table'[Status] = "In progress",
    "In progress",
    IF (
         'Table'[Status] = "Expired",
        "Expired",
        IF (
             'Table'[Status]  = "Completed",
            "Completed",
            IF (
                'Table'[Status] = "Not started"
                    && 'Table'[Certification Expiration Date]  <> BLANK (),
                "Completed",
                "Not started"
            )
        )
    )
)

     


    3.Final output

     

     

    Best regards

    Albert He

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly