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.

I’ve been in this hole all week now so I am turning to brains bigger than my own.

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:

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.