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. 

       

       

       

      • BA_Pete's avatar
        BA_Pete
        Super User
        3 years ago

        Hi kaushikhalvadia ,

         

        Okay, I see. I assumed you wanted to show metrics around these dates rather than just filter the table based on an OR condition. No matter, we've done half the work we need to do anyway.

         

        1) In your modelling screen, double-click the active relationship and, in the dialog, set it to inactive.

         

        2) Update your [_noofOrders] measure to this:

         

        // Now seeing your model, this may actually be called _noofNewCustomers or similar
_noofOrders =
CALCULATE(
    DISTINCTCOUNT(NewCustomer[Number])
    USERELATIONSHIP(CalendarTable[Date], NewCustomer[Date])
)

         

         

        3) Create a new filter measure:

         

        _visualFilter = IF([_noofOrders] > 0 || [_noofFirstOrders] > 0, 1)

         

         

        4) place your [_visualFilter] measure into the visual filter area of your table, then set it to 'is 1'.

         

        This should now work as required.

         

        Pete