Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Filter table rows by Date Table , where Main Table contains dates in 5 columns and can be duplicate

Hi users,    I need to filter my table as per selected date or date range from Date table column (refer left side of the image) and I need to filter weather the range of those dates contains in my ...
General Comment
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous,

    I'd like to suggest you create a duplicate table and apply unpivot columns on the copied table, you can link this new table with raw tables and it does not affect the raw table relationships.

    Regards,

    Xiaoxin Sheng

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

Anonymous , Create a date table join with all dates. One active, all other inactive. Create a formula to count based on all dates using userelation.

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in-power-bi

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thanks amitchandak, Links looks very good, 

     

    But I think those still doesn't clear and gives my answer. I could easily change by date from 'dd mmmm yyyy' to 'dd-mm-yyyy'. 

     

    But I still need to filter out my data as based on the date range. Its a simple filter but my main table does contain duplicate dates and in all 5 column and I need that. In the above shown image , for selected date range, if my data will filter out for right hand side ticked rows, that will be the answer to my current issue.  

     

    Hope it makes sense, what i'm trying to achieve. But if you response does contains the solution, then that probabely I didn't understand it well, i think 🤔.

    • amitchandak's avatar
      amitchandak
      Icon for Super User rankSuper User
      6 years ago

      Anonymous ,Can you share sample data and sample output in table format? 

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        amitchandak Sure you can downlaod a sample file from here Data.xlxs

         

        Column Date1 to Date5 does contains the dates, If my filter date contains in any of these Date Columns (which is from Date1 to Date5), then I need to show the 'ChosenSeat' it in my table view. 

         

        filter date is just a Date Table.