Forum Discussion

PBIDevNoob's avatar
PBIDevNoob
Icon for Helper I rankHelper I
7 years ago

Relationship keys with No Match

Hi! 

 

I have two tables (Main and Category) with a relationship using ID.

 

Table RelationshipData

 

My problem is that not all of the IDs in the main table have categories (in my example, ID 5 is not in the Category table), so when I try to add Main[ID] and Category[Category] to a table visual 5 is left out.

Missing ID 5

I tried using "Show items with no Data", but apparently it treats the entire row as having no data including fields with values so conditional formatting doesn't work properly. In my example below, I added a conditional formatting to ID that would color the value from red (lowest) to green (highest). So, 5 should be highlited as green not red. 

ID 5 is treated as empty

I tried changing the relationship to single direction and even made the relationship inactive, neither worked. 

 

Need Help

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi PBIDevNoob ,

    Please modify relationship direction to single then turn on 'show item with not data' option:

    Regards,

    Xiaoxin Sheng

    • PBIDevNoob's avatar
      PBIDevNoob
      Icon for Helper I rankHelper I
      7 years ago

      Thanks Anonymous but as I said in the post, I've already tried that. The problem is that even if I do manage to show the unmatched items using "Show items with no data", the conditional formatting treats the entire row as blank even if there are values on the field.

       

      Ex. The Conditional formatting set here is to fill the lowest ID with red and the highest ID with green, i.e. 5 should be the greenest color and 1 should be the reddest. But, since it treats ID 5 as blank it was filled with red. 

       ID 5 is treated as empty

      • PBIDevNoob's avatar
        PBIDevNoob
        Icon for Helper I rankHelper I
        7 years ago

        I tried adding a measure, but the result's the same--measure gets filtered or rows get treated as blank in Show Items with No Data. I also can't create a calculated column on Main to get the related category, since it'll return more than one row. I'm out of ideas at the moment.