I have a matrix visual in Power BI that summarize the number of the counts of the field as of the moment of the latest refresh,which changes in real time.
I would like to have another table to record the values of the matrix on daily base every refresh. Right now, I am writing values into the excel table after every refresh. Is there a function in Power BI that enables me to record/track the data automatically on every refresh?
What you are looking for is called Direct Query mode of querying data. Direct Query is often used in calling databases for real time data. To my knowledge, this is not currently supported for excel files. An alternative solution would be to put your excel file on sharepoint/onedrive and use power automate to trigger a refresh whenever there is a change to the excel file. Power Automate, previously called Microsoft Flow, is a great product from Microsoft to help in these kind of tasks. This blog details how you would create such a flow to trigger that refresh after a change has been made to the file.
https://powerbi.microsoft.com/en-us/blog/refresh-your-power-bi-dataset-using-microsoft-flow/