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 re...
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.

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 ,
      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.

  • pankajnamekar25's avatar
    pankajnamekar25
    Super User
    1 year ago

    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.