Dimension tables from SUMMARIZECOLUMNS, that update based on Calendar Table Data selection?

[mmace1]

I have a dataset that is 1 year of data. The dataset is just from an SQL query I wrote. 

From that dataset, I've created Dimensions using SUMMARIZECOLUMNS.  Here's an example of the Cust_MiD_DIM dimension:

Cust_MiD_DIM = 
SUMMARIZECOLUMNS (
    Invoices[MiD],
    Invoices[CustomerNumber],
    "Cust_MiD_SpendPerMonth", DIVIDE ( SUM ( Invoices[PaidAmount] ), [MonthsOfData] ),
    "Cust_MiD_InvoicesPerMonth", DIVIDE ( COUNT ( Invoices[PaymentID] ), [MonthsOfData] ),
    "Cust_MiD_PmtsPerMonth", DIVIDE ( DISTINCTCOUNT ( Invoices[PaymentID] ), [MonthsOfData] ),
    "Cust_MiD_DaysSinceLastPmt", DATEDIFF ( MAX ( Invoices[CreateDateUTC] ), [MostRecentPmt], DAY ),
    "Cust_MiD_DaysSince1stPmt", DATEDIFF (MIN (Invoices[CreateDateUTC] ), [MostRecentPmt],DAY)
)

So great, I can each of the columns from the above dimension into a filter, that the user can use to filter the report. And if we change the underly dataset date range, everything still calculates correctly. 

But, the user would like to be able to filter the dataset by date, and then have that date range affect the dimensions. 

e.g. -

Say  MiD A and CustomerNumber 1 average 10 invoices a month between 6/1/2019 and 6/1/2020.  If the user decides to limit to 1/1/2019 - 6/1/2020 on the calendar filter, they want that Cust_MiD_DIM dimension table to update, so that it reflects the # of invoices per month in that new date range (which maybe is something other than 10). 

I'm a little stumpted how to do that. Is it even possible?  Doesn't SummarizeColumns only update on data refresh, so that this approach 100% can't work for that additional request? 

Is there another way? 

[amitchandak]

@mmace1 , You can create a dynamic table based on slicer selection.

Try SEGMENTATION

https://www.daxpatterns.com/dynamic-segmentation/
https://www.daxpatterns.com/static-segmentation/
https://www.poweredsolutions.co/2020/01/11/dax-vs-power-query-static-segmentation-in-power-bi-dax-po...
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization

[mmace1]

 @amitchandak Thanks!   I (think) I'm familar with the concepts you linked, but I don't think they apply to this situation, do they? 

[amitchandak]

@mmace1 ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[mmace1]

Example Fact Table

Example Dimension Table, that's built off of the above fact table (same dimension as I shared the code for in the first post)

What the user (theoretically) wants to do, is to be able to change that dimesion, say where it says Cust_MiD_SpendPerMonth, and instead of it being based on the full set of data in the Fact table, to have it based on a date range that they select within the report.  Do I make sense? 

[v-lionel-msft · Accepted solution]

Hi @mmace1 ,

Has your problem been solved?

You try to use the [MiD] column to create a relationship between the two tables. Although the data of the calculated table is derived from the fact table, they can not interact if there is no active relationship between the two tables.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.