topic how to use your own table's fields to filter rows from another table? in DAX Commands and Tips

how to use your own table's fields to filter rows from another table?

Anonymous — Wed, 14 Sep 2022 14:09:11 GMT

I'm trying to create a column that counts rows of another table depending on a filter referencing fields in the current table. Can this be done?

Specifics:

I have two tables, with shape as follows:

past_year (has 12 rows):

Month	Start	End
September 2022	Sept 1, 2022	Sept 30, 2022
August 2022	Aug 1, 2022	Aug 31, 2022
July 2022	July 1, 2022	July 31, 2022
...	...	...

events:

idx	Created (date)	Completed (date)
lots of records

I want to create a column in past_year distributing rows from events in a cumulative way into the month bins, such that the created date is less than the start date, and the completed date is less than the completed date. This is the DAX query that would yield the result I want:

past_year[open_cumulative] = CALCULATE(countrows(events), events[Created] < past_year[startdate] && events[Completed] < past_year[End])

This complains about "The expression contains columns from multiple tables...". I also tried calculatetable instead of calculate in variation 1. same error. So I tried the following:

countrows(filter(events, events[Created] < past_year[startdate] && events[Completed] < past_year[End]))

Oddly, this yielded ony a single total applying to the values in the first row. I tried using RELATED to refer to the fields in the same table:

countrows(filter(events, events[Created] < RELATED(past_year[startdate]) && events[Completed] < RELATED(past_year[End])))

It then complains about not being able to find a relationship to itself: "the column 'past_year[start]' doesn't exist or doesn't have a relationship to any table available in the current context."

Re: how to use your own table's fields to filter rows from another table?

Anonymous — Thu, 15 Sep 2022 06:37:16 GMT

HI Anonymous,

It seems like a common date range calculation requirement, you can refer to the following blog 'start date', 'end date' part or try to create a table to expand detailed date records for further calculations:

Before You Post, Read This

Solved: Spread revenue across period based on start and en... - Microsoft Power BI Community
Regards,

Xiaoxin Sheng

Re: how to use your own table's fields to filter rows from another table?

Anonymous — Tue, 20 Sep 2022 11:55:39 GMT

I think I understand - you're saying that the way to get around it is to expand my past_year table to have a row for every day in the past year, and replace my comparison operator filters with table relationships, then use the inherent rollup features to deliver the counting.

I don't yet see how to do it that way for my need, though. I want a cumulative total based on two fields. For example, if an event has created = April 23, 2022 and Completed = Aug 5, 2022, I want that record to add to the displayed count of only April, May, June, July, and August of 2022.

Re: how to use your own table's fields to filter rows from another table?

Anonymous — Tue, 20 Sep 2022 14:19:35 GMT

I took another look at your "Read this first" link and actually found something that seemed to perfectly meet my use case. However...it's not working as advertised.

Here's my modified measure code:

Tickets Open = VAR tmpTickets = ADDCOLUMNS('events',"Closed",if(events[Status] <> "Closed", TODAY(),[Modified])) VAR tmpTable = SELECTCOLUMNS( FILTER( GENERATE( tmpTickets, pastyear ), [Date] >= events[Created] && [Date] <= [Closed] ), "ID",'events'[ID], "Date",[Date] ) VAR tmpTable1 = GROUPBY(tmpTable,[ID],"Count",COUNTX(CURRENTGROUP(),[Date])) RETURN COUNTROWS(tmpTable1)

Result:

Here's the source data table, with a replicated tmpTickets column matching the above logic added for easy reference:

The Sept rollup is correct, but the Aug rollup should be 27, and all the dailies are wrong except the first.