Forum Discussion
Summarize Data With A Table Where Columns Are Custom Date Groupings
Hi jbrewster
I've just managed to look at this again.
I've attached a sample of what I might set up if I were creating this myself.
1. The DateGroupings table is set up as described before, with a many-to-one bidirectional relationship with DateDimension.
2. If Locks, Submissions etc are measures, then we need to convert them into something that can be used as a filter. I would suggest a calculation group with each calculation item corresponding to one of the measures. I have created a calculation group called Measure Selection for this purpose. I also added a grouping column to this calculation group table to group the # and $ measures separately.
There is an example in this article of something similar.
3. Then we can construct a visual like this with the Measure Selection calculation items on the rows and Date Grouping on the columns, with any arbitrary measure in the values (I used a Dummy measure).
4. Filtering on a single cell of this matrix will filter other visuals on the page. Since on of the filters is a calculation item, it will be applied to any measures in those visuals, in this case overriding them with the measure from the relevant row.
In my example, the lower-left visual includes the selected measure (which is overridden by the calculation item), while the lower-right visual just includes column values but a visual-level filter that Dummy is not blank (which ensures it returns rows related to the selection in the matrix).
This is just to illustrate the concept and I expect this will need to be adjusted quite a bit to get the exact behaviour you want 🙂
Regards
Super UserHi jbrewster
First of all, just confirming my understanding: You would like to create a visual similar to the matrix below, and when you select cell(s) of the matrix, the filter corresponding to those cell(s) will be applied to another visual (in this cae a table visual with more detail). Is that right?
For example, if you click on the value 15 (Submitted, This Week), the other table visual will be filtered to rows corresponding to these same filters.
| Today | This Week | |
| Locked | 4 | 12 |
| Submitted | 6 | 15 |
Question: Are "Today" and "This Week" in the above matrix measures or values of a particular column? (From your description it sounds like they are measures.)
The reason I ask is that selecting measures in a visual has no filtering effect on other visuals.
So if "Today" and "This Week" are measures, then selecting a cell of the matrix will only produce a filter corresponding to the row ("Locked or "Submitted") but not the column.
To fix this, you could the model so that "Today" and "This Week" etc are values within a column.
A common method for this is to instead set up the DateGroupings table like the example below.
The Date column of the DateGroupings table should have a many-to-1 bidirectional relationship with DateDimension.
The benefit of this structure is that you just need to filter the Period column without writing any DAX to apply the filter.
| Period | Date |
|---|---|
| This Week | 5-May-24 |
| This Week | 6-May-24 |
| This Week | 7-May-24 |
| This Week | 8-May-24 |
| This Week | 9-May-24 |
| This Week | 10-May-24 |
| This Week | 11-May-24 |
| Today | 7-May-24 |
| Last Month | 1-Apr-24 |
| Last Month | 2-Apr-24 |
| ... | ... |
Here are some posts on this method (sometimes called Period table):
- Creating Current Day, Week, Month And Year Reports In Power BI Using Bidirectional Cross-Filtering A...
- Create Dynamic Periods for Fiscal or Calendar Dates in Power BI
- Custom Date Period Selections in Power BI
You may want to add sort-by columns to this table as well.
If you set up the model this way and used the Period column as the column header of a matrix visual, selecting a particular cell would apply both the row and column as filters to other visuals.
I'll leave it there as I have made a few assumptions about your model.
Is any of the above helpful?
Regards