Forum Discussion

jscottNRG's avatar
jscottNRG
Icon for Helper II rankHelper II
8 years ago
Solved

Single date slicer to filter multiple date tables

I'm hoping this isn't actually so difficult, but I've had a tough time trying to figure out the proper setup. I'd like to use a single date slicer to filter multiple tables with dates in them. The ta...
Need Help
  • edhans's avatar
    edhans
    8 years ago

    Shouldn't your slicer be based on the dates in a Date Table where the Date field is related to all of the relevant date fields in your other tables?

jscottNRG's avatar
jscottNRG
Icon for Helper II rankHelper II
8 years ago

I solved the issue where restricting the date range on the date slicer was causing visuals to go blank.  The problem was that the date fields in RoughInspections and FinalInspections were datetime, and my Calendar table was date only.  I thought I had dropped the time component but had only changed the display format in the Data view.  I had to go back to adjust the query settings like this:  

Transform -> Date Only

 

@ Aree and edhans were right about the relationship problem causing the deactivated relationship between Calendar and RoughInspections.  The existing relationship path from Calendar ->Final Inspection -> Job Info -> Rough Inspection meant there was a conflict when I tried to activate a relationship between Calendar and RoughInspections.  I fixed it by setting the Cross filter direction for the relationship between RoughInpections and Job_info to "Single."

 

Thanks again to everyone who contributed for the help!

edhans's avatar
edhans
Icon for Community Champion rankCommunity Champion
8 years ago

"I solved the issue where restricting the date range on the date slicer was causing visuals to go blank.  The problem was that the date fields in RoughInspections and FinalInspections were datetime, and my Calendar table was date only."

Been there, done that. :smileyhappy:

 

FWIW, you can also just make it Date only by clicking on the calendar icon in the field name, and selecting Date. What you did was extract the date. If you just change the type to date (vs Date/Time, or Date/Time/Timezone) it does the same thing.

 

Honestly, I am not sure what the difference is either. But I've learned to look at the little icon.

  • Calendar - date only
  • Calendar with clock - date/time
  • Globe with clock - date/time/timezone.

Glad you got it all sorted. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    I have implemented the Date Dimension. Two tables Investment logs and Customers are linked as highlighted below. The measusures from the  investment logs tables are responding to the slicers but the measures from the customer table are not responding. The data type for the Customers[daatecreated] and Investmentlogs[trandate]

     have been set to Date , dd/mm/yyyy same goes for the Date Dimension. I will need help form anyone as soon as possible.

  • Aree's avatar
    Aree
    Icon for Resolver I rankResolver I
    7 years ago

    Hey Mate,

     

    try adjusting the cross filter direction to both. You currently have the cross filter direction to single.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago
    Aree, thanks , i have made the change but there was not difference. Below are the measures being calculated for display in both tables Total Subscriptions = Calculate( CountRows(qmInvestmentLogs), qmInvestmentLogs[InvType] = "Subscription",qmInvestmentLogs[TranStatus] = "Success") Total Approved Customers = Calculate(CountRows(qmCustomers),qmCustomers[KYCStatusDesc]= "Approved") Total Subscription Amount = Calculate(Sum(qmInvestmentLogs[Amount] ) ,Filter(qmInvestmentLogs,qmInvestmentLogs[InvType] ="Subscription" && qmInvestmentLogs[TranStatus] ="Success") ) Total Redemptions Amount = Calculate(Sum(qmInvestmentLogs[Amount] ) ,Filter(qmInvestmentLogs,qmInvestmentLogs[InvType] ="Redemption" && qmInvestmentLogs[TranStatus] ="Success") ) All work well but the Total Approved customers.