Forum Discussion

kzmanus's avatar
kzmanus
Advocate I
8 years ago
Solved

Converting a Summary Table to Transaction Table

Would like someone to tell me how to accomplish this. The No. Days Rented column would act as a counter for each row and add the appropriate number of rows to transaction table and update date by 1 w...
Show and Tell
  • AlbertoFerrari's avatar
    AlbertoFerrari
    8 years ago

    Hi Kevin,

     

    another option is to leverage GENERATE, like in:

     

    Transactions = 
GENERATE ( 
    Equip,
    CALENDAR ( 
        Equip[START], 
        IF ( 
            ISBLANK ( Equip[RETURN DATE] ), 
            TODAY (), 
            Equip[RETURN DATE] 
        )
    )
)

    The drawback is that you will have both tables in the model (the base table and the expanded one), the good is that it gives you a lot of flexibility. It all depends on the usage you need to do with the table.

     


    Have fun with DAX!

    Alberto Ferrari
    http://www.sqlbi.com

AlbertoFerrari's avatar
AlbertoFerrari
Most Valuable Professional
8 years ago

Hi Kevin,

 

another option is to leverage GENERATE, like in:

 

Transactions = 
GENERATE ( 
    Equip,
    CALENDAR ( 
        Equip[START], 
        IF ( 
            ISBLANK ( Equip[RETURN DATE] ), 
            TODAY (), 
            Equip[RETURN DATE] 
        )
    )
)

The drawback is that you will have both tables in the model (the base table and the expanded one), the good is that it gives you a lot of flexibility. It all depends on the usage you need to do with the table.

 


Have fun with DAX!

Alberto Ferrari
http://www.sqlbi.com

kzmanus's avatar
kzmanus
Advocate I
8 years ago

Agreed. To bad Power BI doesn't have the connection only setting excel does for power query. Wonder if we can do the summary table in memory?