DAX -Create New table

[ChoiJunghoon]

HI

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

TableA

Category	Charge
A	3
B	4
C	2
D	1

TableB

Index
1
2
3
4
5
6
7
8
9
10

I wanto to create the NewTable 

Result

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

Could you help me ? 

[wdx223_Daniel · Accepted solution]

[Jihwan_Kim · Accepted solution]

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 

[wdx223_Daniel · Accepted solution]