Multiple If AND ELSE querry

[Ibaz]

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 Date	Result
Expired		Expired
Expired	date	Expired
Not started	date	Completed
Not started		Not started
In progress		In progress
In progress	date	In progress
Completed	date	Completed

[Anonymous · Accepted solution]

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]

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]

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

[ValtteriN]

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