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 colum...
Need Help
Tips and Tricks
Tutorial Requests
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

      • kaushikhalvadia's avatar
        kaushikhalvadia
        Regular Visitor
        3 years ago

        Hi BA_Pete 

         

        Thanks for your reply.

         

        I have implemented it as per your suggestion but looks like something I have missed or done wrong. Could you please see the attached file and let me know what is wrong here?

         

        It doesn't filter records as per requirement.

        If I provide 8/6/2018 as both StartDate and EndDate, it displays records with FirstOrderDate in 2010, 2015 and even 1995 so something is wrong here.