Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Cells To Filter Fact Table Via Start and End Date

Hi,   Can someone help me wherein I created a table namely Reporting Start and Reporting End and if I enter the desired day/month/year on any of the cells then the fact table will be filtered?   ...
Need Help
v-hashadapu's avatar
v-hashadapu
Community Support
1 year ago

Hi Anonymous , thank you for reaching out to the Microsoft Fabric Community Forum.

  1. Once you have the filtered table in Power BI, you can export it to Excel by using the "Export Data" feature.
  2. In Excel, you can set up a query that connects to the Power BI dataset. To do this, go to the Data -> Get Data"-> From Power BI.
  3. Sign in to your Power BI account if you aren't already.
  4. You'll see a list of available datasets. Select the dataset that contains your filtered data.
  5. Click "Load" to import the data into Excel. This will create a query that dynamically pulls data from your Power BI dataset.
  6. You can set the query to refresh automatically or manually based on your needs. To do this, right-click on the query in the "Queries & Connections" pane and select "Properties."
  7. In the query properties, you can set the refresh options, such as refreshing the data when the file is opened or at specific intervals.
  8. Any changes in the Reporting Start Date and Reporting End Date will dynamically refresh the data in Excel.

     

 

If this helps, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details.
Thank you.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Thanks Again...

 

The refreshed of query will only happen when I'm logged in the company's intranet and it didn't allow the use of Powerbi so if the report and start date can be done via excel, power query or sql  then the refreshed might happen

  • v-hashadapu's avatar
    v-hashadapu
    Community Support
    1 year ago

    Hi Anonymous , thank you for reaching out to the Microsoft Fabric Community Forum.

    You can use Power Query and Excel formulas to dynamically filter your data based on the Reporting Start and End Dates entered by the user. Here's how you can do it:

    1. In an Excel sheet, create a small table with two columns as Reporting Start Date and Reporting End Date. Name this table tbl_ReportDates.
    2. Go to Data -> Get Data -> From Other Sources -> From SQL Server (or other source, depending on your database). Load the Fact Table into Power Query.
    3. Open Power Query Editor -> Home -> Manage Parameters -> New Parameter:
      Name: paramStartDate

    Type: Date/Time

    Value: =Excel.CurrentWorkbook(){[Name="tbl_ReportDates"]}[Content]{0}[Start Date]

    1. Repeat the same for paramEndDate, linking it to the End Date column.
    2. Apply a filter to your Fact Table in Power Query:
      [FactTable].[Date] >= paramStartDate and [FactTable].[Date] <= paramEndDate
    3. Click Close & Load to return the filtered table to Excel.
    4. Right-click the query in the Queries & Connections pane and select Properties.Check Refresh data when opening the file or manually refresh by clicking Data -> Refresh All.

    Using SQL Query in Excel (Directly from Database)

    If your data is in SQL Server, you can filter it dynamically using Excel's SQL query function.

    1. Go to Data -> Get Data -> From Database -> From SQL Server Database. Enter server name and database name. Click Advanced Options and enter a parameterized SQL query: SELECT * FROM FactTable

    WHERE Date >= (SELECT StartDate FROM tbl_ReportDates)

    AND Date <= (SELECT EndDate FROM tbl_ReportDates)

    2. Load -> Table. Now, whenever you change the Start and End Dates in Excel, refresh the query via Data -> Refresh All.

    If this helps, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details.
    Thank you.