Help with IF Statement Measure that uses AND and OR

[Anonymous]

FileID    Employee    Concerns     Reason 1     Reason 2    Reason 3

1                John         Yes            

2            Emily            No

3              Henry         Yes               health          fatigue

I'm wanting to create an IF statement that will return the FileID and the employee name when I insert this measure into a matrix that has the emploees in the row section and file ID for the columns 

** or a calculated column that will return a value on every row if the below if statement is true 

The IF statement will check to see if Concerns = "Yes" and then check to see if Reason 1, Reason 2 or Reason 3 are blank

I want to see an error made by employees when filling out the form to make sure if they are filling out "yes" for concerns that they are then filling out either reason 1, reason 2 or reason 3 any of them can be filled in but at least one

So something like IF(concerns = "yes" then return 1 if Reason 1 or reason 2 or reason 3 are blank 

and the opposite as well IF(concerns = "no" then all the fields (reason 1, reason 2, and reason 3 should ALL be blank) 

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

You can refer to the DAX below:

Y =
IF (
    MAX ( 'Table'[Concerns] ) = "Y"
        && (
            NOT ( MAX ( 'Table'[Reason 1] ) = "" )
                || NOT ( MAX ( 'Table'[Reason 2] ) = "" )
                || NOT ( MAX ( 'Table'[Reason 3] ) = "" )
        ),
    1,
    IF (
        MAX ( 'Table'[Concerns] ) = "N"
            && MAX ( 'Table'[Reason 1] ) = ""
            && MAX ( 'Table'[Reason 2] ) = ""
            && MAX ( 'Table'[Reason 3] ) = "",
        1
    )
)

If I misunderstood, please provide the result you expect.

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

[Ashish_Mathur]

Hi,

I would suggest that in the query editor, you select t he first 3 columns, right click on the column heading and select "Unpivot other columns".  Then you drag File ID and Name to the visual.  To your visual, drag Attribute to the filter/slicer and select Yes.  Drag File ID to the row labels.  This should show you all those File ID who answered Yes and recorded some reason.