Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
cabliewA
Regular Visitor

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

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):

MonthStartEnd
September 2022Sept 1, 2022Sept 30, 2022
August 2022Aug 1, 2022Aug 31, 2022
July 2022July 1, 2022July 31, 2022
.........

 

events:

idxCreated (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."

3 REPLIES 3
cabliewA
Regular Visitor

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.  

v-shex-msft
Community Support
Community Support

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:

Before You Post, Read This 

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

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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: 

cabliewA_0-1663678770422.png

 

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

cabliewA_1-1663682156848.png

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.