Forum Discussion

LJRDQN's avatar
LJRDQN
Frequent Visitor
5 years ago
Solved

Dynamic chart based on visual table selection, but chart shows expanded dates

To simplify, I have data set ID:   ID info 1 A 2 B 3 C 4 D 5 E   and data set X Date ID 1/1/2020 1 1/1/2020 3 1/2/2...
Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    Hi LJRDQN ,

     

    You can create another dim_date table:

     

    Dimm_Date = CALENDAR(DATE(2020,1,1),DATE(2020,12,31))

     

    Then create one to many relationship between it and fact table.

     

    You can use the following measure to calculate history last week:

    historythisweek = CALCULATE(COUNT(Table2[ID]),FILTER(ALL(Dimm_Date),Dimm_Date[Date] >=MAX(Table2[Date])-7&&Dimm_Date[Date]<=MAX(Table2[Date])),ALLEXCEPT(Table2,Table2[ID]))

     

    Please refer to the pbix file.

     

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

     

    Best Regards,

    Dedmon Dai

     

selimovd's avatar
selimovd
Most Valuable Professional
5 years ago

Hey LJRDQN ,

 

what relationship do you have between the 2 tables?

I guess it should be 1:n and the problem is you filter by date and want to get an information from the table on the 1 site.

 

If that is the case then use CROSSFILTER function within your calculate to make the relationship bidirectional for this measure:

CROSSFILTER(ID[ID], X[ID], Both)

  

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 
LJRDQN's avatar
LJRDQN
Frequent Visitor
5 years ago

the relationship between the ID columns is 1 to many and already has the cross filter direction listed as both