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

[Anonymous]

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 I refresh the report every day and sometimes multiple times a day. After each refresh, 3 measures I've created get updated and I manually store these 3 daily measure values in a power bi ticket trend table where I then display a 'trend graph visual' of tickets each day. Shows the teams involved, progress or otherwise.

My 'Ticket Trends' table just has 4 fields / columns where I manually store the measure values that result after each refresh:

1. Date (The date the refresh occurred)
2. Total Tickets
3. Total Tickets Resolved
4. Total Tickets being Worked

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 in advance for pointing me in the right direction.

Thanks,

Sean.

[Anonymous]

Hi,

The table already exists: 'Ticket Trends' table and has just has 4 olumns to track daily results on 4 key meausres that are updated after every refresh.

The purpose of the table to provide a historical trend over time of the four measures that get updated after each refresh.

Attached is a screenshot of the Ticket Trends table copied to excel, as well as a screenshot of the Power BI Model.

My objective is to automatically update the Ticket Trends table by adding a row and populating the daily measure values after each refresh.

If it would help, a quick 15min call may help?

Thanks again to all for your assistance,

Sean.

[Anonymous]

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 · Accepted solution]

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.

[sevenhills]

Typically, to store persistently, you use power query. Create the same functionality of snaphsot (table) with your needs in Power Query. When you refresh, power query will also refresh this table. This is better in performance as it is written to storage.

If you want to do the same in DAX, create a table using summarize.