Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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)
the relationship between the ID columns is 1 to many and already has the cross filter direction listed as both
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |