Find non matched values between two tables on criteria

[jibran]

Hi Devs,

I have tried my best to use my knowledge on DAX queries and couldn't find out the right formula.

I want to find out the vehicles that doen not match between two tables and I further want to slice them using the product attached to them.

I used the lookup to match the values between two tables but the problem is it does not show value that is not common between both.

Vehicle Lookup = LOOKUPVALUE('FW FM Vehicles'[REGISTRATIONNUMBER],'FW FM Vehicles'[REGISTRATIONNUMBER],'RYF FM Vehicles'[RegistrationNumber])

Then I used if else to find matched and not matched values.

I would like to find the values between two tables based on there registration number and then further slice them on the basis on there prodcut Id that is table 1 (Management Fee ID) and table 2 (RYF FW ID).
So I will have two falgs True and False. On False I want to show values that are not common on both tables.

Thanks for your help. 

[v-qiuyu-msft]

Hi @jibran,

If there is a active relationship between these two tables, you can create a calculated column in the table FW FM Vehicles with Related() function. If the related() function return non blank values, please return TRUE, otherwise it's FALSE. 

Column = IF(ISBLANK(RELATED('RYF FM Vehicles'[RegistrationNumber])),FALSE(),TRUE())

Best Regards,
Qiuyun Yu 

[jibran]

Hi Qiuyun,

Thanks for your reply

If I want to further filter that show me the vehicles that are not common and there FW Fee ID to be like '3'.

I want to show vehicles that are not common and FW Fee ID = 3

[v-qiuyu-msft]

Hi @jibran,

You can modify DAX for calculated column below:

Column = IF(ISBLANK(RELATED('RYF FM Vehicles'[RegistrationNumber])) && [FW Fee ID]=3,FALSE(),TRUE())

Best Regards,
Qiuyun Yu