Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
EricMencarini
Frequent Visitor

Power BI - Refresh Data

 
Hello to everyone! Good evening!

I would appreciate so much if anyone could help me with this:

Context:
For everytime that my dashboard have a 'refresh', I have a card that show the date dd/mmmm/yyyy hh:ss:nn for the last refresh.

 
Measure = 
MAXX(
    {
        MAX(table[column]),
        MAX(table[column])
    },
    [Value]
)

Whcih returns this for example:  23/05/2024 15:23:33 (Latest refresh)
 
But what I want to do is:

(btw I did a lot of research but I couldn't find a solution to it[Youtube, chat gpt, forums])
I just want this measure to be refreshed if the data in Power Bi really changed

What I was thinking is to storage any metric value in the dashboard, and then after the refresh to make a comparison


Example:
Latest refresh : 23/05/2024 15:23:33
Clients = 842

After dashboard refresh:
If Clients > 842 then we get the new date: 24/05/2024 15:23:33
else (Clients = 842 it keeps the date from latest refresh: 23/05/2024 15:23:33

If anyone have any ideas I would appreciate a lot!
Thank you!

1 ACCEPTED SOLUTION
danextian
Super User
Super User

Here's what I am thinking.
In the query editor, create a query that updates every time a semantic model refreshes. This query contains the refresh datetime.

 

=DateTimeZone.SwitchZone( DateTimeZone.LocalNow(), 😎 // the service uses UTC this needs to converted to your local timezone. 8 = UTC+8. This query will always be a single row.

 

In Power Automate, run a query against a dataset.  The query should pickup the refresh datetime from the semantic model + the count of the client. You can simulate a query by creating a CALCULATED table inside Power BI and then transfer the logic to Power Automate. The syntax is similar to that of when using DAX Query or DAX Studio. Here's a sample:

 

DEFINE
    VAR __MAX =
        MAX ( MaxDateFile[Date] )
    VAR __MAXFILE_DU =
        CALCULATE (
            MAX ( MaxDateFile[Filename] ),
            FILTER (
                MaxDateFile,
                MaxDateFile[Date] = __MAX
                    && MaxDateFile[Type] = "DU"
            )
        )
    VAR __MAXFILE_PB =
        CALCULATE (
            MAX ( MaxDateFile[Filename] ),
            FILTER (
                MaxDateFile,
                MaxDateFile[Date] = __MAX
                    && MaxDateFile[Type] = "PB"
            )
        )
    VAR __RESULT =
        ROW (
            "Max Date", __MAX,
            "Max File DU", __MAXFILE_DU,
            "Max File PB", __MAXFILE_PB
        )

EVALUATE
UNION (
    ROW ( "Category", "Max Date:", "Value", FORMAT ( __MAX, "DD MMM YYYY" ) ),
    ROW ( "Category", "DU File:", "Value", __MAXFILE_DU ),
    ROW ( "Category", "PB File:", "Value", __MAXFILE_PB )
)

 

 

Use DEFINE when defining variables. Use EVALUATE instead of RETURN.

 

Append the output to an Excel online table. Connect to the Excel file in Power BI. Load it to the model. Compare the latest count in the Excel file to that of current data.

 

The gist here is that you must be able to store a snapshot of your data somewhere to be able to make a comparison between the previous state of the data and the current.

Disclaimer: I am no Power Automate expert but I checked on the connectors and it seems my proposition is possible.










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

View solution in original post

5 REPLIES 5
danextian
Super User
Super User

Here's what I am thinking.
In the query editor, create a query that updates every time a semantic model refreshes. This query contains the refresh datetime.

 

=DateTimeZone.SwitchZone( DateTimeZone.LocalNow(), 😎 // the service uses UTC this needs to converted to your local timezone. 8 = UTC+8. This query will always be a single row.

 

In Power Automate, run a query against a dataset.  The query should pickup the refresh datetime from the semantic model + the count of the client. You can simulate a query by creating a CALCULATED table inside Power BI and then transfer the logic to Power Automate. The syntax is similar to that of when using DAX Query or DAX Studio. Here's a sample:

 

DEFINE
    VAR __MAX =
        MAX ( MaxDateFile[Date] )
    VAR __MAXFILE_DU =
        CALCULATE (
            MAX ( MaxDateFile[Filename] ),
            FILTER (
                MaxDateFile,
                MaxDateFile[Date] = __MAX
                    && MaxDateFile[Type] = "DU"
            )
        )
    VAR __MAXFILE_PB =
        CALCULATE (
            MAX ( MaxDateFile[Filename] ),
            FILTER (
                MaxDateFile,
                MaxDateFile[Date] = __MAX
                    && MaxDateFile[Type] = "PB"
            )
        )
    VAR __RESULT =
        ROW (
            "Max Date", __MAX,
            "Max File DU", __MAXFILE_DU,
            "Max File PB", __MAXFILE_PB
        )

EVALUATE
UNION (
    ROW ( "Category", "Max Date:", "Value", FORMAT ( __MAX, "DD MMM YYYY" ) ),
    ROW ( "Category", "DU File:", "Value", __MAXFILE_DU ),
    ROW ( "Category", "PB File:", "Value", __MAXFILE_PB )
)

 

 

Use DEFINE when defining variables. Use EVALUATE instead of RETURN.

 

Append the output to an Excel online table. Connect to the Excel file in Power BI. Load it to the model. Compare the latest count in the Excel file to that of current data.

 

The gist here is that you must be able to store a snapshot of your data somewhere to be able to make a comparison between the previous state of the data and the current.

Disclaimer: I am no Power Automate expert but I checked on the connectors and it seems my proposition is possible.










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

I was just thinking something like this, but you make it clear, thank you!!

This pbix might help once you get the tables set up. The logic inside compares the current against the previous state. In the  screenshot below, the current refresh count is 104 which is the same as the previous refresh. The logic will pickup not the latest but the earliest datetime with the same count just after the count has changed to 104.

danextian_0-1716608777572.png

 










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
EricMencarini
Frequent Visitor

Even with a solution outside of Power BI? 
Like Power automate or something related?

v-binbinyu-msft
Community Support
Community Support

Hi @EricMencarini ,

Sorry, so far, to my knowledge, this may not be achieveable.

 

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors