Summarize Data With A Table Where Columns Are Custom Date Groupings

[jbrewster]

I am currently working on a Power BI report where I need to present both a summarized version of the data within a table as well as be able to view the individual rows of the same table but filtered by the summarized information.

I have 3 tables currently:

• A fact table with multiple dates
• A dimension date table
• A dimension date table with the custom date grouping flagged (1 or 0) when a date falls into the grouping

The fact table has 9 dates in total that will be related to the DateDimension table. Currently only 2 dates are related.

I am hoping to create a summarized table where the rows are each of the dates (9 in total) and the columns are the custom groupings in the DateGroupings table (IsToday, PreviousBusinessDay, ThisWeek, LastWeek, ThisMonth, LastMonth, ThisQuarter, LastQuarter, YearToDate). I have successfully created measures that aggregate the information like I want but I don't know how to apply the logic that is in the measures to a table that will show the individual records that make up the measure.

Here is some code from one of the measures I created:

Locks # This Week = 
    CALCULATE(
        COUNTROWS(CLI),
        FILTER(
            DateGroupings,
            DateGroupings[ThisWeek] = 1
        ),
        USERELATIONSHIP(CLI[dateratelock],DateDimension[DateCode])
    )

In this example, I am counting the records where the dateratelock column falls within this week which is flagged in the DateGroupings table. The value being returned is correct.

Ultimately, I am trying to create a table that looks something like this:

	Today	This Week
Locked	4	12
Submitted	6	15

I successfully did this using a matrix table but that is where I am stuck. Clicking on the cells in the matrix table did not filter the standard table I have on the report as I hoped it would.

Any guidance here would be incredible.

[jbrewster]

Thank you @OwenAuger.

Yes, your understanding of what I want the final output to be is correct; a table of values that can be clicked to filter another table.

Once I have the period table built, what is the best way to actually visualize the information on screen? There will be multiple dates being pulled from the fact table (9 in total) to be grouped across these periods. 

Is there a way to have all of that information in a single table? Is that even a good idea?

I really appreciate your help here.

[OwenAuger]

Hi again @jbrewster 

Thanks for confirming!

The best way to visualize would depend on what you are wanting to convey. Is it useful to see Today, Last Week, Last Month, Last Quarter etc all at once?

Also, what did you mean by "9 dates"? I would expect a larger number of dates in the CLI table if it relates to all those different date groupings.

In any case, I can mock up an example model/report to illustrate some ideas. Will send a bit later.

Regards

[jbrewster]

Yes, all at once is the goal here.

As for the dates, there are 9 dates that need to be grouped into those custom groupings. That would mean 81 total "cells" to speak of on the page.

Thank you very much!

[OwenAuger]

Thanks 🙂 Just to make sure I understand this correctly, could you mock up how the 9 dates with 9 custom groupings could look, with an indication of what should be computed in each cell?

Just want to make sure I don't go astray from your requirements!

[jbrewster]

Of course. Here is a screenshot of the system we are replacing with this PowerBI report.

You can ignore the 'X' out columns. Also, in this screenshot, the activites (Locks, Submissions, ect...) are broken out into both units and dollars. The layout of that data isn't required to look exactly like as above but both the units and dollars should be there. The units is a count, the dollars is a sum.

Hope all this makes sense.

Thank you very much.

[OwenAuger]

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

[OwenAuger]

Hi @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