Cater for missing / blank dimension

DAX DAX Calculations
1

Hi,

Hopefully a straight forward one here…

I have a Matrix which reports Sales and Returns for our products. We can have examples where goods are sold or returned and where their ProductID is not in our Products table. Instead of just showing as BLANK in the Matrix, they have asked if I show this as ‘Missing’, so that the users are aware of an un-mapped product.

I have tried various options of ISBLANK, but I think whatever the solution, it needs to be at the Dimension lookup table level.

Please see example pbix attached using some very basic mock up data of Fruit sales and returns: https://www.dropbox.com/s/03nak92utjr1rwr/Null%20in%20Dimension.pbix?dl=0

The DIM table Fruits, has rows for Apples, Bananas and Oranges, but not Pears, where we have sales of 60 in the table Sales.

1a

Instead of the Matrix showing a blank value, I want to replace BLANK with “Missing”.

Many thanks

2

@Mark
Unfortunately this not all that straightforward. Since you want to add something to a row it has to come from a table, which means having to use a combination of ROW, UNION, EXCEPT, etc. This is actually something I’ve been working on for myself, so I know it’s doable. I will try to get something posted tomorrow.

Nick

Enterprise%20DNA%20Expert%20-%20Small

3

Actually, thought of an easier way to accomplish this.

In Power Query:

  1. Append Sales into Fruits
    Append
    Append537×758 9.19 KB
  2. Remove Duplicates of Fruit code
    Remove%20Dupes
  3. Only keep FruitCode and FruitName columns
  4. Add custom column that if Fruit Name= null then “Missing” else FruitName
    Add%20Custom%20coumn
    Add%20Custom%20coumn960×527 15 KB
  5. Remove the old FruitName column
  6. Set data types
    Final%20Dim%20Table
    Then use Fruit Name on rows, and a simple sum measure:
    Final%20Table

Here’s the file:
Null in Dimension.pbix (57.0 KB)

Nick

Enterprise%20DNA%20Expert%20-%20Small

4

Hi Nick,

Thank you very much for taking the time to look at this for me and providing a detailed solution.

I am currently away from work, so I am not able to test it against my proper model until next week, however on my return I will check it out in detail and report back!

Thanks again,

Mark

5

Hi Nick,

Finally been able to look at this and your solution works great - just what I needed.

Many thanks

Mark