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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
powerbiexpert22
Impactful Individual
Impactful Individual

filter fact table using parameter power bi

i have created report which consist of sales fact table with order date column, direct query mode is used for the sales fact table, i want to filter this report using order date range parameters in power bi service. how to do this?

4 REPLIES 4
powerbiexpert22
Impactful Individual
Impactful Individual

Hi @rajendraongole1 ,

 

I have implemnted above m code , i am getting below error , tables name is sales in database

 

powerbiexpert22_0-1719764859354.png

 

powerbiexpert22_1-1719764889890.png

 

powerbiexpert22_2-1719764950213.png

 

powerbiexpert22_3-1719764979283.png

 

 

rajendraongole1
Super User
Super User

Hi @powerbiexpert22 -Please create a seperate date table will help with filtering the data from order date as below

create a new calculated table for Date table , instead of using Orderdate to filter the data.

DateTable = CALENDAR(MIN(SalesFactTable[OrderDate]), MAX(SalesFactTable[OrderDate]))

 

give the relationship between DateTable[Date] --> to SalesFact[OrderDate] 

create two parameters using whatif parameters with name StartDate and EndDate. Click on "New Parameter" -> "What-if Parameter".

 

 

use the above parameters in your main sales measure as below

create a measure that uses to filter the data on your sales table.

 

SalesFilteredByDate =
CALCULATE(
[Total Sales],
SalesFactTable[OrderDate] >= [StartDate] && SalesFactTable[OrderDate] <= [EndDate]
)

 

you can place the slicers to your report using the startdate and enddate parameters.

 

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!





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

Proud to be a Super User!





Hi @rajendraongole1 ,

 

can it be possible to create date parameters in power query and filter the sales fact table based on that parameter?

Hi @powerbiexpert22 - Yes, it is possible with Manage parameter option in Power query editor too

 

Manage Parameters -> New Parameter 

create StartDate with Date data type provide a default value . EndDate another paramter Date and provide a default value.

Near to the M Code:

 

let
Source = (StartDate as date, EndDate as date, SalesTable as table) as table =>
let
FilteredTable = Table.SelectRows(SalesTable, each [OrderDate] >= StartDate and [OrderDate] <= EndDate)
in
FilteredTable
in
Source

 

Hope it helpful, Try the above approach if you required to implement in PQ.

 

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

 





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

Proud to be a Super User!





Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.