Trying to filter or summarize a table by rolling criteria from another table

[CSnyderMPH]

In Dynamics, I have a satisfaction survey that is sent to clients automatically a set duration after a service was delivere to clients.

I have a table of responses to these customer voice surveys in Dynamics, and a table of services provided, and I am trying to make a table that ties the two together, but the issue I am experiencing is that I believe I am trying to filter my rows with rolling or variable filters in them.

For example, I want to make sure that I am filtering my services so that they hit a rolling criteria of 30 days preceeding a given survey invite that was sent to that client. Client A's survey window of services and time will be different from Client B''s, and so on.

All of the examples that I have seen using DAX use static filters, which won't work, because each client will have one or more 30 day windows that I am trying to filter on.

I have the unique ID's and date ranges I am interested in, but I am unsure how to proceed with filtering my table of services where each row will have different date filter criteria based on the client ID present in that row.

I have a table of responses to surveys, with the date ranges that I want to pull from, but this will of course be a different date range for each entity that it is regarding. I want to filter a second dable using the date ranges that I have here, and the ragarding ID, to make one table of all services that would have been rendered to that ID, in the date ranges relevant to that survey.

As for sudocode or comparison, in R, my preferred language for this kind of manipulation, I would have been able to take a fact table of ranges, and to a loop to filter through my second table, where:

for (i in table1rows) {

filter table2rows where

[id] = table1rows[id] &&

[date] > table1rows[start of date range] &&

[date] < table1rows[end of date range]

}

and just append the results of each filtered subset into a new table to work with.

Any assistance would be greatly appreciated.

[Kedar_Pande]

Filtered Services =
GENERATE(
'Survey Table',
VAR CurrentSurveyDate = 'Survey Table'[Survey Date]
VAR CurrentClient = 'Survey Table'[Client ID]
VAR StartDate = CurrentSurveyDate - 30
RETURN
FILTER(
'Services Table',
'Services Table'[Client ID] = CurrentClient &&
'Services Table'[Service Date] >= StartDate &&
'Services Table'[Service Date] <= CurrentSurveyDate
)
)

If this answer helped, please click Kudos or mark as Solution.
-Kedar
LinkedIn: https://www.linkedin.com/in/kedar-pande

[Praful_Potphode]

Hi @CSnyderMPH 
As mentioned by @lbendlin  please provide sample data

[DataNinja777]

Hi @CSnyderMPH ,

You can create your required output by creating a calculated table like below to bridge the two fact tables:

ServicesInWindow = 
GENERATE (
    'SurveyResponses',
    
    -- Variables for the current survey row
    VAR vInviteDate = DATEVALUE('SurveyResponses'[Invite Date])
    VAR vClientID = 'SurveyResponses'[Regarding ID]
    
    -- 1. This is your original CALCULATETABLE logic,
    --    now stored in a variable.
    VAR vFilteredServices = 
        CALCULATETABLE (
            'Services',
            FORMAT('Services'[Regarding ID], "0") = vClientID,
            'Services'[Service Date] > vInviteDate - 30,
            'Services'[Service Date] <= vInviteDate
        )
    
    -- 2. This is the fix: We select only the columns we want
    --    from the filtered services, and we intentionally
    --    LEAVE OUT 'Services'[Regarding ID] to avoid the
    --    duplicate name.
    RETURN
    SELECTCOLUMNS(
        vFilteredServices,
        "ServiceID", 'Services'[ServiceID],
        "Service Date", 'Services'[Service Date],
        "Notes (for demo)", 'Services'[Notes (for demo)]
    )
)

The resultant output is shown in the 3rd table:

Please find attached the pbix file for your reference.

Best regards,

[CSnyderMPH]

Thank you @DataNinja777 !

I think this is what I am looking for, except flipped. I was hoping to achieve a longer join, so for each invite I am expecting one or more rows of services, or rather, I am expecting each service to potentially have an invite associated with it.

I also noticed that in my case, it does not seem to be working in the event that there are more than one matches; I expected multiple services to match to one invite, and the function appears to only return values where there is only one match available.

Apologies, I am unfamiliar with the use of return within the generate funciton itself, as that is inconsistent with the MS documentation on the funciton.

GENERATE function (DAX) - DAX | Microsoft Learn

Sample Responses:

[Response ID]	[Invite ID]	[Regarding ID]	[Invite Date]
R1	I1	HH1	2025-01-01
R2	I2	HH2	2025-01-02
R3	I3	HH3	2025-01-03
R4	I4	HH4	2025-01-04
R5	I5	HH5	2025-01-05
R6	I6	HH6	2025-01-06
R7	I7	HH7	2025-01-07
R8	I8	HH8	2025-01-08
R9	I9	HH9	2025-01-09
R10	I10	HH10	2025-01-10
R11	I11	HH11	2025-01-11
R12	I12	HH12	2025-01-12
R13	I13	HH13	2025-01-13
R14	I14	HH14	2025-01-14
R15	I15	HH15	2025-01-15
R16	I16	HH16	2025-01-16
R17	I17	HH17	2025-01-17
R18	I18	HH18	2025-01-18
R19	I19	HH19	2025-01-19
R20	I20	HH20	2025-01-20

Sample Outputs:

[Output]	[Regarding ID]	[Output Date]
O1	HH1	2024-12-01
O2	HH1	2024-12-02
O3	HH2	2024-12-03
O4	HH2	2024-12-04
O5	HH3	2024-12-05
O6	HH3	2024-12-06
O7	HH4	2024-12-07
O8	HH4	2024-12-08
O9	HH5	2024-12-09
O10	HH5	2024-12-10
O11	HH6	2024-12-11
O12	HH6	2024-12-12
O13	HH7	2024-12-13
O14	HH7	2024-12-14
O15	HH8	2024-12-15
O16	HH8	2024-12-16
O17	HH9	2024-12-17
O18	HH9	2024-12-18
O19	HH10	2024-12-19
O20	HH10	2024-12-20

Sample Desired Outcome:

[Output]	[Regarding ID]	[Output Date]	[Response ID]	[Invite ID]	[Invite Date]
O1	HH1	2024-12-01	R1	I1	2025-01-01
O2	HH1	2024-12-02	R1	I1	2025-01-01
O3	HH2	2024-12-03	R2	I2	2025-01-02
O4	HH2	2024-12-04	R2	I2	2025-01-02
O5	HH3	2024-12-05	R3	I3	2025-01-03
O6	HH3	2024-12-06	R3	I3	2025-01-03
O7	HH4	2024-12-07	R4	I4	2025-01-04
O8	HH4	2024-12-08	R4	I4	2025-01-04
O9	HH5	2024-12-09	R5	I5	2025-01-05
O10	HH5	2024-12-10	R5	I5	2025-01-05

[CSnyderMPH · Accepted solution]

Replying to myself to post a solution that does what I described, but I'm sure is quite inelegant and time-inefficient:

EVALUATE
// Raw crossjoin with a cart product of both tables
var cjtab =
	CROSSJOIN(
		MSD_Program_Outputs, // Table of all outputs, which could be associated to a survey
		MSFP_Voice_SurveyResponseTrace // Table of all responses, which have one or more outputs that would have triggered sending the survey
	)

// Filtering logic required	
var filttab = 
	FILTER(
		cjtab,
		[Regarding ID] = [nwc_outputhousehold] && // Cull the cart prod table to only include matching rows
		[Output Date] < [Invite Date] && // Output has to have occurred before the survey invite
		[Output Date] >= [Invite Date] - 30 // Output has to have occurred within 30 days of the survey invite
	)
	
return filttab

[lbendlin]

RETURN is not part of any particular DAX function, it is part of the general DAX syntax, like EVALUATE, DEFINE and VAR

Defining variables in DAX queries - SQLBI

In your use case you create a dimension table over [Response ID], then join both fact tables against that, and then you can use a standard table visual without any code.

[CSnyderMPH]

Apologies if I misunderstand. Yes, I understand return as a part of the dax syntax, but not how it is relevent in the context of the Generate functions, since that should only be accepting two arguments, as two tables?

How would that join go about filtering either table in your recommendation then? Ideally, I want to do this without creating any new relationships that may cause the dreaded ambiguity betwen my extant filters and relationships.

[lbendlin]

Any argument in any DAX function can be a full DAX script as long as it returns the right data type.

For example 

VAR A = SUMMARIZE(VAR B = 'Dates' RETURN B,[Date])

RETURN A

 I want to do this without creating any new relationships 

Please reconsider.  If your data model is sound (star/snowflake schema, no bidirectionals, no linked fact tables, no bridge tables)  then you should be able to cover all scenarios.

[CSnyderMPH]

I appreciate the best practice of relationships, but as I cannot make a relationship across a sliding date range, I do not see how I can implement this into my solution.

If you have any guidance into dynamic relationships, please let me know. Thank you.

[lbendlin]

for dynamic relationships you can use measures as TREATAS to transfer filters between unrelated tables.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...