Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Create dimension table from 2 different tables

Hi, how to create a dimension table from 2 different tables without including the (Blank) value? Below doesn't work as it includes (Blank) as one of the value which cause many to many relationship. ...
Need Help
  • whereismydata's avatar
    whereismydata
    5 years ago

    updated version:

    Country = 
VAR vFilter = {"","null","(Blank)",BLANK()}
VAR vTableA = SELECTCOLUMNS('Table A',"Country",'Table A'[Country])
VAR vTableAFilter = FILTER(vTableA,NOT( [Country] in vFilter))
VAR vTableB = SELECTCOLUMNS('Table B',"Country",'Table B'[Country])
VAR vTableBFilter = FILTER(vTableB,NOT( [Country] in vFilter))
VAR vUnion = UNION(vTableAFilter,vTableBFilter)

return vUnion

     

    This is a more dynamic approach,

    1) select define values

    2) select columns from source table A | B

    3) Filter vTableA|B

    4) Union

     

     

whereismydata's avatar
whereismydata
Icon for Resolver IV rankResolver IV
5 years ago

Hi Anonymous 

 

not sure if the most elegant solution, but it works:

 

Country = 
VAR vTableA = SELECTCOLUMNS('Table A',"Country",'Table A'[Country])
VAR vTableAFilter = FILTER(vTableA,[Country] <> BLANK() && [Country] <> "(Blank)" && [Country] <> "" && [Country] <> "" && [Country] <> "null")
VAR vTableB = SELECTCOLUMNS('Table B',"Country",'Table B'[Country])
VAR vTableBFilter = FILTER(vTableB,[Country] <> BLANK() && [Country] <> "(Blank)" && [Country] <> "" && [Country] <> "" && [Country] <> "null")
VAR vUnion = UNION(vTableAFilter,vTableBFilter)

return vUnion

 

 

whereismydata's avatar
whereismydata
Icon for Resolver IV rankResolver IV
5 years ago

updated version:

Country = 
VAR vFilter = {"","null","(Blank)",BLANK()}
VAR vTableA = SELECTCOLUMNS('Table A',"Country",'Table A'[Country])
VAR vTableAFilter = FILTER(vTableA,NOT( [Country] in vFilter))
VAR vTableB = SELECTCOLUMNS('Table B',"Country",'Table B'[Country])
VAR vTableBFilter = FILTER(vTableB,NOT( [Country] in vFilter))
VAR vUnion = UNION(vTableAFilter,vTableBFilter)

return vUnion

 

This is a more dynamic approach,

1) select define values

2) select columns from source table A | B

3) Filter vTableA|B

4) Union

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Thank you so much whereismydata ! It works.