Forum Discussion

MarkSL's avatar
MarkSL
Helper V
7 years ago
Solved

Cater for missing / blank dimension

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'.  

 

I have tried various options of ISBLANK, but 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.

 

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

 

 

 

 

 

 

 

 

 

Many thanks

 

Mark

Need Help
  • v-piga-msft's avatar
    v-piga-msft
    7 years ago

    Hi MarkSL ,

    By my tests, to get your desired output, we could create a calculated column with RELATED fucntion in Sales table and change the relationship like below.

    relationship.PNG

    Please refer to the fomula below to create the calculated column.

    Column =
VAR a =
   RELATED ( Fruits[FruitName])
RETURN
    IF ( ISBLANK ( a ), "Missing", a )

    Then you could create the Matrix with the calculated column as Rows field.

    Here is my test result.

    Capture.PNG

    More details, please refer to my attachment.

    Best Regards,

    Cherry

     

3 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar
    7 years ago

    Hi MarkSL ,

    By my tests, to get your desired output, we could create a calculated column with RELATED fucntion in Sales table and change the relationship like below.

    relationship.PNG

    Please refer to the fomula below to create the calculated column.

    Column =
VAR a =
   RELATED ( Fruits[FruitName])
RETURN
    IF ( ISBLANK ( a ), "Missing", a )

    Then you could create the Matrix with the calculated column as Rows field.

    Here is my test result.

    Capture.PNG

    More details, please refer to my attachment.

    Best Regards,

    Cherry

     
    • MarkSL's avatar
      MarkSL
      Helper V
      7 years ago

      Hi Cherry, 

       

      Apologies for my late response to your reply and thank you for taking the time to look at my query.

       

      Whilst your solution of the calculated column on the Sales table does fix the issue, it only does so for a missing item in Sales.  Whereas I need to be able to cater for an unknown  / missing item being present in either the Sales or Returns tables.  I therefore think any solution / calculated column needs to be in the Fruits table itself?

       

      I also try to avoid using Bi-Directional cross filters as much as possible after being told by Alberto Ferrari that you will go to hell :smileylol:

       

      I'll keep researching a solution and will report back here if I find one.

       

      Mark

  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar
    7 years ago

    Hi MarkSL ,

    Have you solved the problem?

    If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

    If you still need help, please feel free to ask.
    Best Regards,

    Cherry