The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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?
Hi @rajendraongole1 ,
I have implemnted above m code , i am getting below error , tables name is sales in database
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!!
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!!
Proud to be a Super User! | |
User | Count |
---|---|
77 | |
75 | |
36 | |
31 | |
29 |
User | Count |
---|---|
94 | |
80 | |
55 | |
48 | |
48 |