Forum Discussion

kaushikhalvadia's avatar
kaushikhalvadia
Regular Visitor
3 years ago

Use single Slicer to filter rows for multiple date column

I am a beginner at Power BI and currently facing an issue while developing a report.

 

I have created SQL query which pulls information from multiple tables and the query returns around 10-12 columns of which two are Date and FirstOrderDate columns.

Example:

 

 

On Report, I will use a single slicer(Between types). 

When the user selects any date range from the slicer at that time I want the report to filter records based on both columns(Date and FirstOrderDate)

 

 

Need Help
Tips and Tricks
Tutorial Requests

8 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    3 years ago

    Hi kaushikhalvadia ,

     

    I'm detecting an XY Problem here.

    Can you give details around what metrics/values you want to be able to calculate in your report please? It's most likely that you just need to create the correct relationships and measures, rather than worry about how your table gets filtered.

     

    Pete

    • kaushikhalvadia's avatar
      kaushikhalvadia
      Regular Visitor
      3 years ago

      Hi BA_Pete 

       

      I need this filter to be applied and I'll use COUNT to get the number of orders within that date range. The SQL query which I already have created has OrderNumber column along with Date and FirstOrderDate so I'll use COUNT to check the total number of orders.

       

      Example.

       

       

      Thanks

      • BA_Pete's avatar
        BA_Pete
        Super User
        3 years ago

         

        Ok, I think I have an idea of what you're after.

        1) Create a calendar table and mark it as a date table. There's thousand of articles of how to do this online so I'll not go into detail here. FWIW, I would recommend using an M code calendar and putting it into a Dataflow, so it's always accessible to future reports.

        2) Relate your calendar table to your fact table in the model as follows:

        - calendar[Date] ONE : MANY fact[Date] (ACTIVE)

        - calendar[Date] ONE : MANY fact[FirstOrderDate] (INACTIVE)

        3) Create your measures:

         

        // Count of orders
_noofOrders =
DISTINCTCOUNT(fact[Number])

// Count of first orders
_noofFirstOrders =
CALCULATE(
    DISTINCTCOUNT(fact[Number])
    USERELATIONSHIP(calendar[Date], fact[FirstOrderDate])
)

         

         4) Use calendar[Date] in your slicer and any visuals where you want to show values vs date.

         

        Pete