Dax

[manoj619]

In power bi i have shrepoint list Table a with two columns one column which will be used as drop-down visual(lookup values) and another column data which will be compared to table b column need to return values which are not present in table a from table b based on drop down from table a
Table a
Car Country Number
tata india 1
tata swiss 2
swift pak 3
benz swiss 4
swift India 5
kia pak 6
Polo. India. 7
Byd India 8

Table b
Brand year
benz 2001
kia 2002
tata 2003
Volvo 2004

example:
if i select pak in dropdown the output should be
Benz 2001
tata 2003
Volvo 2004

If i select india the output should be
benz 2001
Kia 2002
Volvo 2004

[v-pagayam-msft]

Hi @manoj619 ,

We are following up once again regarding your query. Could you please confirm if the issue has been resolved on your end?

If the issue has been resolved, we kindly request you to share the resolution or key insights here to help others in the community.If our response addressed your query,consider accepting it as solution.

Thank you for your understanding and participation.

[v-pagayam-msft]

Hi @manoj619 ,
I wanted to check in on your situation regarding the issue. Have you resolved it? If you have, please consider marking the reply that helped you or sharing your solution. It would be greatly appreciated by others in the community who may have the same question

Thank you.

[pankajnamekar25]

Hello Manoj,

You can try  below solution

Ensure Table A[Country] is related to the dropdown visual.

No direct relationship is needed between Table A[Car] and Table B[Brand].

Create a Measure to Find Missing Values

DAX

Missing Brands =

VAR SelectedCountry = SELECTEDVALUE('Table A'[Country])

VAR BrandsInTableA =

    FILTER('Table A', 'Table A'[Country] = SelectedCountry)

VAR MissingBrands =

    FILTER('Table B',

        NOT 'Table B'[Brand] IN VALUES(BrandsInTableA[Car])

    )

RETURN

    MissingBrands

Thanks,
Pankaj

If this solution helps, please accept it and give a kudos, it would be greatly appreciated.

[manoj619]

Thanks, but i need it in table format

[pankajnamekar25]

Hello @manoj619 

try this

Missing Brands Table =
VAR SelectedCountry = SELECTEDVALUE('Table A'[Country])

RETURN
FILTER(
'Table B',
NOT 'Table B'[Brand] IN
SELECTCOLUMNS(
FILTER('Table A', 'Table A'[Country] = SelectedCountry),
"Car", 'Table A'[Car]
)
)

Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.

[v-pagayam-msft · Accepted solution]

Hi @manoj619 ,
Thank you @pankajnamekar25  for the prompt response!

The measure cannot return a table. Since you want a table format, you may use a table visual with a measure to filter missing brands dynamically. Please follow these steps:

• Add Table B in a table visual, then create a measure using below :

  ShowMissingBrands =

  VAR SelectedCountry = SELECTEDVALUE('Table A'[Country])

  
  

  VAR BrandsInTableA =

      CALCULATETABLE(

          VALUES('Table A'[Car]),

          'Table A'[Country] = SelectedCountry

      )

  
  

  VAR MissingBrands =

      EXCEPT( VALUES('Table B'[Brand]), BrandsInTableA )

  
  

  RETURN

      IF( SELECTEDVALUE('Table B'[Brand]) IN MissingBrands, 1, 0 )
  
• Apply a visual filter on ShowMissingBrands where value = 1.This will dynamically display missing brands based on the selected country in the slicer.
  

This approach will give the expected output dynamically.For clear understanding ,refer the attached file.

Hope this helps.If so,consider accepting it as a solution.

Thank you for being a valued member in Microdoft Fabric Community Forum!

Regards,
Pallavi.

[v-pagayam-msft]

Hi @manoj619 ,
Could you please confirm if the issue has been resolved? If a solution has been found, it would be greatly appreciated if you could share your insights with the community. This would be helpful for other members who may encounter similar issues.
Thank you for your understanding and assistance.