filter fact table using parameter power bi

[powerbiexpert22]

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?

[powerbiexpert22]

Hi @rajendraongole1 ,

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

[rajendraongole1]

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!!

[powerbiexpert22]

Hi @rajendraongole1 ,

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

[rajendraongole1]

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!!