time filter creation

[Katerina_SL]

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

[Bijen_Shah]

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]

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]

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!

[Katerina_SL]

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.