Month Filter

[Anonymous]

Hi All,

I have two connected tables #Opens & #Clicks. "ID" is the common key or common column among the two tables and both have "dates" column. I have created a Card Visual for CTOR (Count of Clicks / Count of Opens). I also have to apply a date slicer for the visual but it doesmn't work. I am new to this. Kindly help.

[v-janeyg-msft · Accepted solution]

Hi， @Anonymous 

It’s my pleasure to answer for you.

According to your description,I think you can create a date table first,and create two relationships with the two tables,then create two measure to calculate the desired result.

Like this:

Table 2 = CALENDAR(DATE(2019,1,1),DATE(2020,12,31))

COUNT of clicks = CALCULATE(COUNTROWS('#Clicks'),ALLSELECTED('Table 2'[Date]))

COUNT of open = CALCULATE(COUNTROWS('#Open'),ALLSELECTED('Table 2'[Date]))

Here is my sample .pbix file.Hope it helps.

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , if open and click has many to many join, They should be joined. You should create common dimesions/master table+ date table and join these two with that.

refer: https://www.sqlbi.com/articles/the-importance-of-star-schemas-in-power-bi/

https://www.seerinteractive.com/blog/join-many-many-power-bi/

And :https://www.youtube.com/watch?v=Bkf35Roman8