Forum Discussion

Katerina_SL's avatar
Katerina_SL
Helper II
2 years ago

time filter creation

Hello everyone! 

 

I would like to ask help regarding creating filter.

 

I have 2 table.

Table 1 contain:

  • Offer
  • Time of Offer Creation

Table 2 contain:

  • Offer (connection with table1)
  • Option (1 offer can have more than 1 option)
  • Responsible
  • Time of Option Creation

Also I created table Calendar and make connection with table 1. With table 2 I can't create active connection because I also need to have connection between offer and option table via offers.

 

I need to create matrix with following data:

  • Responsible
  • Number of Offers (measure)
  • Number of Options (measure)

Also I would like to use time filter. But here problem appears. If it possible somehow to use 1 filter for both measure but for calculation of number of Offer will be used offer_creation time and for calculation of number of options will be use option_creation time?

Why I need it? Because it's possible that offer was created in january and option was created in february.

 

Thank you in advance for any ideas,

Ekaterina

Need Help

4 Replies

  • Bijen_Shah's avatar
    Bijen_Shah
    Regular Visitor
    2 years ago

    Sol 1:

    You can use role-playing dimensions
    Have 2 similar calendar tables - 1st connected to offer date and 2nd connected to option date
    Then you can use two different date hierarchies on 2 different tables to filter your analysis.

    Refer more on  https://learn.microsoft.com/en-us/power-bi/guidance/star-schema

    Sol 2:

    If its a fairly small report then you can think of merging the two tables and creating a single table architecture.
    This could be an optimum way if you evaluate using dax studio on how much cpu consumption your queries take.

    Here's a video which helped me evaluate the architecture - https://www.youtube.com/watch?v=qEWrYO1ioe0

     

    Did I answer your question? Mark my post as a solution!
    Appreciate your Kudos!! Happy to Help! Katerina_SL 

    • Katerina_SL's avatar
      Katerina_SL
      Helper II
      2 years ago

      Thank you for reply! I will check video.

      Regarding 1st solution, I think I will not able to use it because I need to have these 2 measures in one table and at the end have the sum of them. That's why I can't use 2 different filters.

      I was thinking maybe it's possible to include this somehow into function for these measures. But I don't have idea how to do it.

    • Katerina_SL's avatar
      Katerina_SL
      Helper II
      2 years ago

      Hello!

      I've checked links which you sent me but I still have questions. 

      Okay, I will create extra table with date which will connect with option creation time and will use 2 filters in the report instead of 1. But the problem is that I need to use 1 filter only for some columns and 2 filter - only for others columns in the same table and don't touch values in previous columns.

      And also the question: if it's possible to use this dates in functions? Because I'm going to create calcuated table from this data after. 

       

      Thank you,

      Ekaterina.

      • Bijen_Shah's avatar
        Bijen_Shah
        Regular Visitor
        2 years ago

        Hi Katerina_SL ,

         

        Could you please share a sample data file and also share some specific expected calculations(visual) examples? sharing a pbix would be great.

        1. I think I need to understand the specific use to understand how we can use different or same filter condition.
        Also, meanwhile it would be nice to have a look at DAX functions which can help ignore filter context like ALL, ALLSELECTED, ALLEXCEPT. (use 1 or combination) with edit interactions on visual level to affect filters specifically from different filters.

        2. Do you want to create a summary table in the data model? Then you can use SummarizeColumns, Summarize functions.


        Bijen

        Did I answer your question? Mark my post as a solution!
        Appreciate your Kudos!! Happy to Help!