Relationship keys with No Match

[PBIDevNoob]

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. 

[Calup]

Hi, did you solve this issue?

[mussaenda]

[v-shex-msft]

Hi @PBIDevNoob ,

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

Regards,

Xiaoxin Sheng

[PBIDevNoob]

Thanks @v-shex-msft 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]

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. 

[v-shex-msft]

HI @PBIDevNoob,

I'd like to suggest you use fields value with condition formula to do color formatting and add conditions to check blank records and return result.
When you use divide or other auto detect mode, it will recognize unmatched or blank records as zero.

Regards,

Xiaoxin Sheng