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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Div
Frequent Visitor

How to create a table which lists the count for each refresh date?

I have connected a SQL database which gets refreshed every month.  I need a table which lists the count for each month. Can someone help me with this please?

1 ACCEPTED SOLUTION
v-yulgu-msft
Employee
Employee

Hi @Div,

 

How does your SQL table refresh? Will the new data cover the old one? In other words, the table always shows data for only one month which is the latest one.

 

If that is the case, what you want looks like an incremental load feature so that all history data can be stored, however this is not supported by Power BI now. Here is an idea where PG said this feature will be released in the future. As a workaround, you could export each month's data from SQL database to an Excel file, place these monthly excel files in the same folder. Then, in Power BI desktop you can get data via "Folder" connector.

 

While if the new updated data won't cover the older one, the new month's data is just appended to the original table, you just need to import data from SQL database and refresh it every month. To get the count value per month in a summary table, please create a calculated table with this similar DAX: (suppose there existing a date field in source table)

New Table =
SUMMARIZE (
    DataTable,
    DataTable[DateColumn].[Year],
    DataTable[DateColumn].[Month],
    "CountValue"COUNT ( DataTable[ValueColumn] )
)
 
Best regards,
Yuliana Gu
Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-yulgu-msft
Employee
Employee

Hi @Div,

 

How does your SQL table refresh? Will the new data cover the old one? In other words, the table always shows data for only one month which is the latest one.

 

If that is the case, what you want looks like an incremental load feature so that all history data can be stored, however this is not supported by Power BI now. Here is an idea where PG said this feature will be released in the future. As a workaround, you could export each month's data from SQL database to an Excel file, place these monthly excel files in the same folder. Then, in Power BI desktop you can get data via "Folder" connector.

 

While if the new updated data won't cover the older one, the new month's data is just appended to the original table, you just need to import data from SQL database and refresh it every month. To get the count value per month in a summary table, please create a calculated table with this similar DAX: (suppose there existing a date field in source table)

New Table =
SUMMARIZE (
    DataTable,
    DataTable[DateColumn].[Year],
    DataTable[DateColumn].[Month],
    "CountValue"COUNT ( DataTable[ValueColumn] )
)
 
Best regards,
Yuliana Gu
Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Count of what? Can you specify with examples?

Div
Frequent Visitor

Example: I have a table which lists a set of computer devices. It also shows how many are sold out as of now.

10 devices listed out of which 4 have been sold out.

Next month when the data gets refreshed, the count of computer devices sold out will increase to 6. So i need another table which lists ( kind of tracking each month's count)

Oct 2018   4

Nov 2018  6

and so on.... 

 

 

 

Anonymous
Not applicable

First, Import the table from the database into the PBI data model.

 

Generate an additional table withing PBI, using CALENDAR function and group it by MONTH, using SUMMARIZE. Get the required Sums from the base table that you imported from the database.

Additionally, you can provide a screenshot of how your base table in the database looks like, and I might write the dax for you.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.