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 Catergory Value A 5 B 8 C 12   TableB Catergory Charge A 2 B 3 C 4   Re...
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]
)
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]
)