Forum Discussion

ChoiJunghoon's avatar
ChoiJunghoon
Icon for Helper III rankHelper III
5 years ago
Solved

Create New Table with Dax

Hello  

I want to create the result table with Dax. 
Could you help me ? 
TableA

CatergoryValue
A5
B8
C12

 

TableB

CatergoryCharge
A2
B3
C4

 

ResultTable

CategoryNumber
A6
A7
B9
B10
B11
C13
C14
C15
C16
DAX
  • daxer-almighty's avatar
    daxer-almighty
    5 years ago

     

    SELECTCOLUMNS(
    GENERATE(
        TableA,
        var Val = TableA[Value]
        var Cat = TableA[Category]
        var Chrg = 
            LOOKUPVALUE(
                TableB[Charge],
                TableB[Category], Cat,
                BLANK()
            )
        var Nums = 
            SELECTCOLUMNS(
                GENERATESERIES(
                    Val + 1, 
                    Val + Chrg,
                    1
                ),
                "@Number", [Value]
            )
        return
            Nums
    ),
    "Category", TableA[Category],
    "Number", [@Number]
)

3 Replies

  • daxer-almighty's avatar
    daxer-almighty
    Icon for Solution Sage rankSolution Sage
    5 years ago

     

    SELECTCOLUMNS(
    GENERATE(
        TableA,
        var Val = TableA[Value]
        var Cat = TableA[Category]
        var Chrg = 
            LOOKUPVALUE(
                TableB[Charge],
                TableB[Category], Cat,
                BLANK()
            )
        var Nums = 
            SELECTCOLUMNS(
                GENERATESERIES(
                    Val + 1, 
                    Val + Chrg,
                    1
                ),
                "@Number", [Value]
            )
        return
            Nums
    ),
    "Category", TableA[Category],
    "Number", [@Number]
)
  • daxer-almighty's avatar
    daxer-almighty
    Icon for Solution Sage rankSolution Sage
    5 years ago

    Another way would be:

    SELECTCOLUMNS(
    GENERATE(
        NATURALINNERJOIN(
            TableA,
            TableB
        ),
        var Val = TableA[Value]
        var Cat = TableA[Category]
        var Chrg = TableB[Charge]
        var Nums = 
            SELECTCOLUMNS(
                GENERATESERIES(
                    Val + 1, 
                    Val + Chrg,
                    1
                ),
                "@Number", [Value]
            )
        return
            Nums
    ),
    "Category", TableA[Category],
    "Number", [@Number]
)