Forum Discussion

Gjakova's avatar
Gjakova
Post Patron
3 years ago
Solved

How to get missing values dynamically with DAX

Hi there, 

I have the following question:

 

I have two lists. Table A and Table B.

CountryCity
USANew York
USALos Angeles
GermanyBerlin

 

Table B looks like:

Country2

City2

USA

Los Angeles

SpainMadrid
AustraliaMelbourne

 

Table A and B can both change depending of my YEAR and MONTH filters, so I cannot use calculated columns or do this in Power Query.

I want to select a certain month and year, and depending on that, the values from table A and B change. So for 2022 November, it looks like above.

 

What I want to do is the following: I want to see which cities from Table A are missing in Table B. These results will be in my new table, which I will call Table C. Or I will conditionally format the cities that are missing.

So the results needs to look like, because those two cities are not in table B (now LA and Berlin are highlighted).

CountryCity
USANew York
USALos Angeles
GermanyBerlin

 

My current formula looks like this and I think it works, but I wanted to know if there is perhaps a better solution?

 

No Match =
IF(ISBLANK(LOOKUPVALUE(test[City2], test[City2], MIN(test[City]))), 1, 0)
Need Help
Show and Tell
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    I am not sure how your datamodel looks like, but I tried to create a sample pbix file like below.

    Please check the below picture and the attached pbix file whether it suits your requirement.

     

     

     

    No match measure V1: =
SUMX (
    DISTINCT ( 'Table A'[City] ),
    CALCULATE (
        IF (
            COUNTROWS ( 'Table A' ) > 0,
            IF (
                COUNTROWS (
                    FILTER ( 'Table B', 'Table B'[City2] IN DISTINCT ( 'Table A'[City] ) )
                ) = 0,
                1,
                0
            )
        )
    )
)

     

     

     

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    3 years ago

    Hi,

    I am not sure how your datamodel looks like, but I tried to create a sample pbix file like below.

    Please check the below picture and the attached pbix file whether it suits your requirement.

     

     

     

    No match measure V1: =
SUMX (
    DISTINCT ( 'Table A'[City] ),
    CALCULATE (
        IF (
            COUNTROWS ( 'Table A' ) > 0,
            IF (
                COUNTROWS (
                    FILTER ( 'Table B', 'Table B'[City2] IN DISTINCT ( 'Table A'[City] ) )
                ) = 0,
                1,
                0
            )
        )
    )
)

     

     

     

    • Gjakova's avatar
      Gjakova
      Post Patron
      3 years ago

      Thank you! This did work, I only had to create a table column but that was 1 minute work. Thanks again 😉