Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Dax for New Table

Hello All,

Hope you are all doing good,

I have 2 tables

table one

regiondomainValues_table1
NASub12
NAMain2
NAAd3
GPUSD5
GPLocal2

 

Table 2

regiondomainValues_tables2
NASub8
NAMain9
NAAd4

APAC

IND6
APAC

 

East2

 

I have created a custom table with distinct values of the above 2 tables

regiondomain
NASub
NAMain
NAAd

APAC

IND
APAC

 

East
GPUSD
GPLocal

 

But when i am using the custom table to show case the values

i am getting summarized duplicate like below

regiondomainValues_table1Values_tables2
NASub1721
NAMain1721
NAAd1721

APAC

IND 8
APAC

 

East 8
GPUSD7 
GPLocal7 

 

But this is what i need to show detailed table like below , how can i achieve this  

regiondomainValues_table1Values_tables2
NASub128
NAMain29
NAAd34

APAC

IND 6
APAC

 

East 2
GPUSD5 
GPLocal2 
Need Help
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    Hi Anonymous ,

     

    I created this Custom table via two steps.

    1 Create a new table.

     

    Custom Table = 
var _1 = GROUPBY('Table 1','Table 1'[region],'Table 1'[domain])
var _2 = GROUPBY('Table 2','Table 2'[region],'Table 2'[domain])
return
DISTINCT(UNION(_1,_2))

     

    2 Add two columns via the following code.

     

    Values_table1 =
CALCULATE(
    MAX( 'Table 1'[Values_table1] ),
    FILTER(
        'Table 1',
        [region] = EARLIER( 'Custom Table'[region] )
            && [domain] = EARLIER( 'Custom Table'[domain] )
    )
)
Values_table2 =
CALCULATE(
    MAX( 'Table 2'[Values_table2] ),
    FILTER(
        'Table 2',
        [region] = EARLIER( 'Custom Table'[region] )
            && [domain] = EARLIER( 'Custom Table'[domain] )
    )
)

     

    Result:

     

    I put the pbix file in the end you can refer.

     

    Best Regards

    Community Support Team _ chenwu zhu

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Anonymous , You need to create a concatenated key in table 3 , Table1 and Table2 and join Table3 with 1,2 on that key

     

    And use these two columns from Table 3 in visual

     

    key = [region] &"-" & [domain]

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous ,

     

    I created this Custom table via two steps.

    1 Create a new table.

     

    Custom Table = 
var _1 = GROUPBY('Table 1','Table 1'[region],'Table 1'[domain])
var _2 = GROUPBY('Table 2','Table 2'[region],'Table 2'[domain])
return
DISTINCT(UNION(_1,_2))

     

    2 Add two columns via the following code.

     

    Values_table1 =
CALCULATE(
    MAX( 'Table 1'[Values_table1] ),
    FILTER(
        'Table 1',
        [region] = EARLIER( 'Custom Table'[region] )
            && [domain] = EARLIER( 'Custom Table'[domain] )
    )
)
Values_table2 =
CALCULATE(
    MAX( 'Table 2'[Values_table2] ),
    FILTER(
        'Table 2',
        [region] = EARLIER( 'Custom Table'[region] )
            && [domain] = EARLIER( 'Custom Table'[domain] )
    )
)

     

    Result:

     

    I put the pbix file in the end you can refer.

     

    Best Regards

    Community Support Team _ chenwu zhu

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.