Forum Discussion

powerbiexpert22's avatar
powerbiexpert22
Impactful Individual
2 years ago

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?

Need Help

4 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    2 years ago

    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's avatar
      powerbiexpert22
      Impactful Individual
      2 years ago

      Hi rajendraongole1 ,

       

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

      • rajendraongole1's avatar
        rajendraongole1
        Super User
        2 years ago

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