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 Synta...
  • 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
     

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