How to get missing values dynamically with DAX

[Gjakova]

Hi there, 

I have the following question:

I have two lists. Table A and Table B.

Country	City
USA	New York
USA	Los Angeles
Germany	Berlin

Table B looks like:

Country2	City2
USA	Los Angeles
Spain	Madrid
Australia	Melbourne

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

Country	City
USA	New York
USA	Los Angeles
Germany	Berlin

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)

[Jihwan_Kim · Accepted solution]

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]

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