Forum Discussion

ChoiJunghoon's avatar
ChoiJunghoon
Helper III
5 years ago
Solved

DAX -Create New table

HI

I can't think how to create new table by using DAX formula. 

TableA

CategoryCharge
A3
B4
C2
D1

 

TableB

Index
1
2
3
4
5
6
7
8
9
10

 

I wanto to create the NewTable 

Result

IndexCategory
1A
2A
3A
4B
5B
6B
7B
8C
9C
10D

 

Could you help me ? 

 

DAX
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    Hi, ChoiJunghoon 

    Please check the below picture and the sample pbix file's link down below.

     

     

    Result Table =
    VAR rankAaddcolumn =
    ADDCOLUMNS ( TableA, "@rankcategory", RANKX ( TableA, TableA[Category],, ASC ) )
    VAR cumulateaddcolumn =
    ADDCOLUMNS (
    rankAaddcolumn,
    "@cumulatemax",
    SUMX (
    FILTER ( rankAaddcolumn, [@rankcategory] <= EARLIER ( [@rankcategory] ) ),
    TableA[Charge]
    )
    )
    VAR cumulateaddcolumnstep2 =
    ADDCOLUMNS (
    cumulateaddcolumn,
    "@cumulatemin",
    SUMX (
    FILTER ( cumulateaddcolumn, [@rankcategory] = EARLIER ( [@rankcategory] ) - 1 ),
    [@cumulatemax]
    )
    )
    VAR addtoindexcolumn =
    ADDCOLUMNS (
    TableB,
    "Category",
    SUMMARIZE (
    FILTER (
    cumulateaddcolumnstep2,
    [@cumulatemin] < TableB[Index]
    && [@cumulatemax] >= TableB[Index]
    ),
    TableA[Category]
    )
    )
    RETURN
    addtoindexcolumn

     

     

    https://www.dropbox.com/s/voz6y9o9e04cht3/choijunghoon.pbix?dl=0 

     

     

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    5 years ago

    Hi, ChoiJunghoon 

    Please check the below picture and the sample pbix file's link down below.

     

     

    Result Table =
    VAR rankAaddcolumn =
    ADDCOLUMNS ( TableA, "@rankcategory", RANKX ( TableA, TableA[Category],, ASC ) )
    VAR cumulateaddcolumn =
    ADDCOLUMNS (
    rankAaddcolumn,
    "@cumulatemax",
    SUMX (
    FILTER ( rankAaddcolumn, [@rankcategory] <= EARLIER ( [@rankcategory] ) ),
    TableA[Charge]
    )
    )
    VAR cumulateaddcolumnstep2 =
    ADDCOLUMNS (
    cumulateaddcolumn,
    "@cumulatemin",
    SUMX (
    FILTER ( cumulateaddcolumn, [@rankcategory] = EARLIER ( [@rankcategory] ) - 1 ),
    [@cumulatemax]
    )
    )
    VAR addtoindexcolumn =
    ADDCOLUMNS (
    TableB,
    "Category",
    SUMMARIZE (
    FILTER (
    cumulateaddcolumnstep2,
    [@cumulatemin] < TableB[Index]
    && [@cumulatemax] >= TableB[Index]
    ),
    TableA[Category]
    )
    )
    RETURN
    addtoindexcolumn

     

     

    https://www.dropbox.com/s/voz6y9o9e04cht3/choijunghoon.pbix?dl=0