topic Re: Adding conditions to an IF statement for blanks in DAX Commands and Tips

Adding conditions to an IF statement for blanks

ld17 — Fri, 03 Feb 2023 14:17:54 GMT

I have a matrix to show completion dates for trainings for users. On the advice of a solution in this forum, I have cross joined tables and used a DAX formula so that if a cell shows "blank," it means that they were not assigned it. However, my issue is that I need to set a few additional conditions for this, as a blank can also mean a few other things: if a user currently has a training "in progress" or is "registered" but not started. Therefore, I would like the blanks to be transformed to these respective values when this is the case. I believe I just need to add a few extra conditions to my IF statement, but I cannot figure out how, given that I am not a DAX expoert. The screenshots below show my codes, the column I would like to reference, and the table I am trying to use to achieve this.

Table = 

CROSSJOIN ( VALUES ( 'Site Services Training Matrix'[User - User Full Name]), VALUES ( 'Site Services Training Matrix'[Training - Training Title] ))

Completion Date = VAR vResult =

    CONVERT (

        LOOKUPVALUE (

            'Site Services Training Matrix'[Transcript - Transcript Completed Date],

            'Site Services Training Matrix'[User - User Full Name], 'Table'[User - User Full Name],

            'Site Services Training Matrix'[Training - Training Title], 'Table'[Training - Training Title]

        ),

        STRING

    )

RETURN

    IF ( ISBLANK ( vResult ), "Not Assigned", vResult)

Re: Adding conditions to an IF statement for blanks

FreemanZ — Fri, 03 Feb 2023 14:36:28 GMT

hi ld17

what is your question or expectation?

Re: Adding conditions to an IF statement for blanks

ld17 — Fri, 03 Feb 2023 15:02:18 GMT

I am wondering how I can add the conditions "in progress" or "registered" to the IF ISBLANK statement below?

    IF ( ISBLANK ( vResult ), "Not Assigned", vResult)

I only want the cell to display "not assigned' if the user didn't receive the training at all. Otherwise, I need it the cell to read "in progress" or "not started," but I cannot figure out how to write this DAX. It should read from this column:

Currently, the DAX formula is transforming all blanks as shown below, but some cells need to read "in proress" or "not started."

Re: Adding conditions to an IF statement for blanks

FreemanZ — Fri, 03 Feb 2023 15:13:08 GMT

hi ld17

change to something like this:

IF ( vResult IN {BLANK(), "in progress", "registered"}, "Not Assigned", vResult)

Re: Adding conditions to an IF statement for blanks

ld17 — Mon, 06 Feb 2023 14:12:03 GMT

Thank you for the response! I tried to add that to my DAX expression, but it did not seem to pull in all the statuses. It seems to only want to give me two:

I guess my issue that I need to reference both of the below highlighted columns in one DAX expression. If a person has completed the training, I want the date to display in the cell of that user's name in my visualization. Otherwise, I would like "in progress," "in progress/past due," or "registered," to display in the cell. But since the date comes from one column (transcript-transcript completed date), and the status comes from a different column ((transcript-transcript status), I am not sure what DAX expression to write to include both.

After some tweaks, I have found that this DAX expression will produce the status, but removes the date since the date is in a separate column:

Completion Date = VAR vResult =

    CONVERT (

        LOOKUPVALUE (

            'Site Services Training Matrix'[Transcript - Transcript Status],

            'Site Services Training Matrix'[User - User Full Name], 'Table'[User - User Full Name],

            'Site Services Training Matrix'[Training - Training Title], 'Table'[Training - Training Title]

        ),

        STRING

    )

RETURN

      IF ( ISBLANK ( vResult ), "Not Assigned", vResult)

And this, the original I was working with, shows the date and "not assigned," but I cannot figure out how to get it to grab the other statues ("in progress, "in progress / past due," "or registered," since they are in a different column:

Completion Date = VAR vResult =

    CONVERT (

        LOOKUPVALUE (

            'Site Services Training Matrix'[Transcript - Transcript Completed Date],

            'Site Services Training Matrix'[User - User Full Name], 'Table'[User - User Full Name],

            'Site Services Training Matrix'[Training - Training Title], 'Table'[Training - Training Title]

        ),

        STRING

    )

RETURN

    IF ( ISBLANK ( vResult ), "Not Assigned", vResult)

Re: Adding conditions to an IF statement for blanks

ld17 — Mon, 06 Feb 2023 19:10:05 GMT

Figured this out with the help of another user. Thanks for all of the help!