Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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:
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."
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.
HI @cabliewA,
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:
Solved: Spread revenue across period based on start and en... - Microsoft Power BI Community
Regards,
Xiaoxin Sheng
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
31 | |
16 | |
11 | |
10 | |
8 |
User | Count |
---|---|
62 | |
20 | |
11 | |
11 | |
11 |