Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Automatically store measures in a table after a refresh on a daily basis.

Hi,   I have what I hope is a simple request and I'm looking for a solution you may be able to help with.   I have a Power BI report that has a data source (production support ticket source) and ...
Need Help
Show and Tell
Tips and Tricks
  • sevenhills's avatar
    sevenhills
    3 years ago

    If you can provide examples with some sample data , it will help us to answer. Any aggregations to present persistently, I prefer doing in power query.

     

    Since you want to do DAX, it is definitely possible. Try looking into these and see if that helps. Summarize, UNION, SELECTCOLUMNS...

     

    DATATABLE: try checking this article

    https://www.sqlbi.com/articles/create-static-tables-in-dax-using-the-datatable-function/

     

    Random example to help you understand and say it can be done.

    I am creating a table using UNION of two tables. Each table has to be same number of columns and data types. 

    Table = 
UNION (
    CALCULATETABLE(
        SUMMARIZE (
            Sales,
            'Product'[Category],
            'Date'[YYYY-MM],
            "Measure", "Count",
            "Count", 'Sales Order'[Orders count]
        )
        , Year('Date'[Date]) = 2020
    )
    ,
    CALCULATETABLE(
        SUMMARIZE (
            Sales,
            'Product'[Category],
            'Date'[YYYY-MM],
            "Measure", "Sales",
            "Sales", [Total Sales]
        )
        , Year('Date'[Date]) = 2020
    )
)

    Data view tab:

     

    I hope this gives some direction. If this is not what you need, Sorry, please could you provide more info.

Anonymous's avatar
Anonymous
Not applicable
3 years ago

The recommended solution above seems more complex than what I was hoping for.

In terms of: "If you want to do the same in DAX, create a table using summarize."

I already have a table created and 'simply' want to add a new row with 3 measures + the date.

 

What I would like to do is remove the manual step of updating the 'Ticket Trends' table after each refresh.

After each refresh, could I instead automatically do the following:

  • Update the 4 fields above
  • If multiple refresh occurs on the same date, then no need to update the 'Date' column and instead just update the remaining 3 columns with measure values from the most recent refresh

 

Thanks,

Sean.

 

  • sevenhills's avatar
    sevenhills
    Icon for Super User rankSuper User
    3 years ago

    If you can provide examples with some sample data , it will help us to answer. Any aggregations to present persistently, I prefer doing in power query.

     

    Since you want to do DAX, it is definitely possible. Try looking into these and see if that helps. Summarize, UNION, SELECTCOLUMNS...

     

    DATATABLE: try checking this article

    https://www.sqlbi.com/articles/create-static-tables-in-dax-using-the-datatable-function/

     

    Random example to help you understand and say it can be done.

    I am creating a table using UNION of two tables. Each table has to be same number of columns and data types. 

    Table = 
UNION (
    CALCULATETABLE(
        SUMMARIZE (
            Sales,
            'Product'[Category],
            'Date'[YYYY-MM],
            "Measure", "Count",
            "Count", 'Sales Order'[Orders count]
        )
        , Year('Date'[Date]) = 2020
    )
    ,
    CALCULATETABLE(
        SUMMARIZE (
            Sales,
            'Product'[Category],
            'Date'[YYYY-MM],
            "Measure", "Sales",
            "Sales", [Total Sales]
        )
        , Year('Date'[Date]) = 2020
    )
)

    Data view tab:

     

    I hope this gives some direction. If this is not what you need, Sorry, please could you provide more info.