DAX - IF lookup value empty return column value

2
votes

I'm using Power Pivot 2013, I have two table.

(fact)Table A: Name and Value (dim) Table B: Name and Type

When selecting pivottable, I want to show Type and Value but if Name.TableA can't be found in Name.TableB, instead of returning (blank) i want pivottable to return Name.TableA. I have tried VALUES() IF(VALUES) with no success.

Thank you in advance.

2
powerpivotdax
What is the source for TableA and TableB? Did you load through Power Query? – GregGalloway
For Table A its through Power Query for Table B, its defined in a sheet. – Mr Shayne

2 Answers

1
votes

i think that you have created a relation between name.tableA and name.TableB. You can Create a calculated column on TABLEA using related(Type.TableB).

At this point if you have a relation between the table in new column on TableA you have some row blank and some row with the TYpe.TableB. If it's working change the column formula with

 =if(ISBLANK(related('TableB'[Type]));'tableA'[name];related('TableB'[Type]))

If there isn't a connection between table you should change related with Lookup.

0
votes

I use a named variable inside the measure for that:

var mylookup = LOOKUPVALUE(
      dim[name]
    , dim[id]
    , fact_table[id]
    ) 
    return IF( NOT ISBLANK (mylookup), mylookup, "UFO")

UFO value will be returned either:

  1. if dim table contains NULL in the dim[name] field,
  2. if there is no match for [id] in dim table.

See more DAX VAR defining named variables in the middle of the measure code