Forum Discussion

manoj619's avatar
manoj619
Regular Visitor
1 year ago
Solved

Dax

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

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

7 Replies

  • pankajnamekar25's avatar
    pankajnamekar25
    Super User
    1 year ago

    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's avatar
      manoj619
      Regular Visitor
      1 year ago

      Thanks, but i need it in table format

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.