Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Power BI Desktop: Why does my mapping table not work for empty cells?

Hello! I have Data about employees with a status abbreviation that can either be "e", "m" or empty and I want to map that status with a different translation table that maps "e" onto "Employee", "m"...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi  Anonymous ,

    According to your description, I created some data to show:

    Here are the steps you can follow：

    1. Create measure.

    table_mapping_table1 =
SWITCH(
    TRUE(),
    MAX('Table'[Value])="e",CALCULATE(MAX('Table (2)'[LongText]),FILTER('Table (2)','Table (2)'[Value]="e")),
    MAX('Table'[Value])="m",CALCULATE(MAX('Table (2)'[LongText]),FILTER('Table (2)','Table (2)'[Value]="m")),
    MAX('Table'[Value])=BLANK(),CALCULATE(MAX('Table (2)'[LongText]),FILTER('Table (2)','Table (2)'[Value]="null")))

    2. Result

    You can downloaded PBIX file from here.

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Anonymous ,

 

Thanks for your help, your measure works! I had another problem though, but your solution helped me figure out why it did not work for me yet:

 

The problem was that I have to have a bidirectional relationship between these two tables, because I have to filter in both directions. And exactly this bidirectional relationship caused the empty values to be filtered out.

 

To fix the problem I added a new column for your table (2):

 

Value2 = IF('Table (2)'[Value] == "null", "", 'Table (2)'[Value])

 

Then I connected both tables via this new column with a bidirectional relationship (Table[Value] and Table(2)[Value2]). Now the table was displayed correctly. I also found out that you can set 'Show Items with No Data' for the elements of a table visual but for me it worked even without this option.

 

Best Regards,

 

JM