topic Re: 2 Tables: Value of column to be blank if the other column value is blank. in DAX Commands and Tips

2 Tables: Value of column to be blank if the other column value is blank.

Sunnie — Wed, 17 Mar 2021 15:40:33 GMT

Hello! I've been spending multiple hours over the last several days trying to get this formula to work. I have two date columns, from two different tables, the tables have a relationship.

I need to have dax look for a blank value from the 1st table column 'STEP Projects' [PM/AppTransitionMtg] to then replace the date value in the 2nd table column 'STEP Resource Planning' [Start] to blank . If no blank is found from the 1st table, then 2nd table column 'STEP Resource Planning' [Start] remains with its original value.

Here are some formulas that I tried, nothing worked.

StartISBlank = SWITCH(LOOKUPVALUE('STEP Projects'[PM/AppTransitionMtg],[PM/AppTransitionMtg],"",'STEP Resource Planning'[Start],""))

StartISBlank = IF(isblank('STEP Resource Planning'[Start]),LOOKUPVALUE('STEP Projects'[PM/AppTransitionMtg],'STEP Projects'[PM/AppTransitionMtg],'STEP Resource Planning'[Start],'STEP Projects'[PM/AppTransitionMtg]))

StartISBlank = MAXX(FILTER('STEP Projects','STEP Projects'[PM/AppTransitionMtg]='STEP Resource Planning'[Start]),'STEP Projects'[PM/AppTransitionMtg])

StartISBlank = LOOKUPVALUE('STEP Projects'[PM/AppTransitionMtg],'STEP Projects'[PM/AppTransitionMtg],'STEP Resource Planning'[Start])

Kept getting errors like; LOOKUPVALUE needs argument 2 or a 3... and "" , BLANK() , wasn't working either. the last formula didn't throw errors, but only returned blank. Here is a pic of the visual with the two data columns : so the Start column with 4/5/2021 should be blank because the PM//..mtg column is blank, and so forth..

Do both columns need to be formatted as a date? and how do I use blank value in a formula?. Helping me would be greatly appreciated, if you could please write out the formula for me.

Re: 2 Tables: Value of column to be blank if the other column value is blank.

ERD — Mon, 22 Mar 2021 09:09:41 GMT

Hello Sunnie ,

From your explanation I see 2 approaches here.
1) If you just want to have a column showing either blank value or value from 'STEP Resource Planning' [Start], then you can creare a calculated column , for example, in 'STEP Projects' table. The code can be like this:

New Start = IF(ISBLANK('STEP Projects'[PM/AppTransitionMtg]), BLANK(), RELATED('STEP Resource Planning'[Start]))

2) If you want to change an existing [Start] column values of 'STEP Resource Planning' table then you need to use Power Query, not DAX. In this case you might need a custom lookup function as described here:
Replicate doing an Excel VLOOKUP in M
with final code as a step like this

#"Replaced Value" = Table.ReplaceValue(#"Previous step name", each [Start], each if fxLookup([Id], #"STEP Projects", "Id", "PM/AppTransitionMtg") = null then null else [Start], Replacer.ReplaceValue,{"Start"})

* [Id] here is a common column (used also to build relations).

Re: 2 Tables: Value of column to be blank if the other column value is blank.

Sunnie — Tue, 23 Mar 2021 13:46:16 GMT

Much appreciated. I tried your formulas, and both worked. Your 2nd formula is what I needed , and received the below formula last week.

Mtg Column =

VAR Mtg = CALCULATE(VALUES('STEP Projects'[PM/AppTransitionMtg]), TOPN(1,FILTER('STEP Projects','STEP Projects'[Id]='STEP Resource Planning'[Project Online NameId])))

RETURN

IF(ISBLANK(Mtg),BLANK(),[Start])