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. Country City USA New York USA Los Angeles Germany Berlin   Table B looks like: Country2...
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
            )
        )
    )
)

     

     

     

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 😉