CODEWITHSUNDEEP
powerbidax
titatovenaar
titatovenaar

Reputation: 309

DAX Query Get Distinct values from Multiple Columns from Multiple Tables

I am trying to get my distinct values in DAX from multiple columns originating from multiple tables into one bridge table.

For example, I have Table A and Table B that both have at least the columns Country and City, but only together I get all the distinct values.

Table A
Country City        Transaction
US      Washington  A1
US      New York    A2
Germany Berlin      A3
France  Lyon        B1
US      New York    B2
Germany Berlin      B3
France  Lyon        B4

And

Table B
Country City    Transaction
US      Houston 1   
France  Paris   2
France  Paris   3
Togo    Lome    4
France  Paris   5
Togo    Lome    6

Together should form:

Table C 
US      Houston
US      Washington
US      New York
Germany Berlin
France  Lyon
France  Paris
Togo    Lome

Any suggestions?

Upvotes: 1

Views: 6963

Answers (1)

BarneyL
BarneyL

Reputation: 1362

It should be something like this:

VAR TableASummary = SUMMARIZE('Table A','Table A','Table A'Country,'Table A'City)
VAR TableBSummary = SUMMARIZE('Table B','Table B','Table B'Country,'Table B'City)
VAR AllRows = UNION (TableASummary, TableBSummary)

EVALUATE
SUMMARIZE(AllRows, Country, City)

Upvotes: 1

Related Questions