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

[LJRDQN]

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/2020	3
1/2/2020	4
1/4/2020	3
1/5/2020	3
1/5/2020	1

the data set tables are connected by a relationship between the ID columns. I have a visual table filtered by a date slicer to show yesterdays IDs. So if yesterday was 1/5/2020 the visual would show:

Date	ID	Info
1/5/2020	3	C
1/5/2020	1	A

Upon selecting a row in the visual table I'd like a chart to show the history of the ID in data set X. So if the row with "ID 3" is selected and the date range was by week, then the chart would show that it showed up 4 times this last week, 0 the week before, etc.

Couldn't get it to work with visual interactions and couldn't figure out a measure. Any help would be appreciated

[v-deddai1-msft · Accepted solution]

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]

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

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[LJRDQN]

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