Calculated column between many to many relations tables in DAX.

[Anonymous]

Hi All,

I have to create a calculated column in one of the many to many relationship tables , requirement is For a loan there can be multiple status dates so  when STATUS_DATE (Table 1) is falling between BK_FILED_DATE and BK_RELEASED_DATE (Both columns from Table2) resultant should maximum status date.

I have resolved many to many in model by using bridge table , but in DAX i am not able to do it in DAX.

Thanks in advance.

[Anonymous]

Below is the requirement converted to DAX, But since FS_HIST & BK_HIST are many to many relation in Dax, i am getting memory issue during the runtime. 

Test = CALCULATE(MAX(FORECLOSURE_STATUS_HISTORY[STATUS_DATE_CYMD]),FILTER(FORECLOSURE_STATUS_HISTORY,FORECLOSURE_STATUS_HISTORY[STATUS_DATE_CYMD]>=BANKRUPTCY_HISTORY[BK_FILED_DATE] &&FORECLOSURE_STATUS_HISTORY[STATUS_DATE_CYMD]<=BANKRUPTCY_HISTORY[BK_RELEASED_DATE]))

[Anonymous]

Hi, @Anonymous 

Given the complexity of many-to-many relationships and the memory issues you're experiencing, it's critical to approach this with efficiency in mind.

Assuming you've resolved a many-to-many relationship using a bridge table, make sure that the table is properly indexed and contains only the columns that are needed to establish the relationship. This can significantly reduce memory overhead. For more insights on managing many-to-many relationships, please refer to the following links:

Model relationships in Power BI Desktop - Power BI | Microsoft Learn

Given memory issues and the need to efficiently handle many-to-many relationships, it is recommended to use a DAX function that minimizes memory usage. Your current approach with and is usually correct, but let's make sure it's optimized. Below is a slightly modified version of the DAX formula:

Test = 
VAR MaxDateFilter = 
    CALCULATETABLE(
        VALUES(BANKRUPTCY_HISTORY[BK_FILED_DATE], BANKRUPTCY_HISTORY[BK_RELEASED_DATE]),
        CROSSFILTER(BANKRUPTCY_HISTORY[KeyColumn], BRIDGE_TABLE[KeyColumn], Both),
        CROSSFILTER(FORECLOSURE_STATUS_HISTORY[KeyColumn], BRIDGE_TABLE[KeyColumn], Both)
    )
RETURN
    CALCULATE(
        MAX(FORECLOSURE_STATUS_HISTORY[STATUS_DATE_CYMD]),
        FILTER(
            FORECLOSURE_STATUS_HISTORY,
            FORECLOSURE_STATUS_HISTORY[STATUS_DATE_CYMD] >= MINX(MaxDateFilter, BANKRUPTCY_HISTORY[BK_FILED_DATE]) &&
            FORECLOSURE_STATUS_HISTORY[STATUS_DATE_CYMD] <= MAXX(MaxDateFilter, BANKRUPTCY_HISTORY[BK_RELEASED_DATE])
        )
    )

If you're still experiencing memory issues, it may be helpful to look at the size and complexity of the tables involved. Reducing the granularity of the data or the number of columns in the table involved in the calculation can sometimes reduce the pressure on memory. For more details on managing memory issues, especially in the context of XMLA endpoint operations, check out the following links:

Troubleshoot XMLA endpoint connectivity in Power BI - Power BI | Microsoft Learn

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data)

Best Regards

Yongkang Hua

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

[TomMartens]

Hey @Anonymous ,

please create a pbix that contains sample data but still reflects your semantic model (tables, relationships, calculated columns, and measures). Upload the pbix to OneDrive, Google Drive, or dropbox and share the link in this thread. If you are using a spreadsheet to create/import the sample data instead of the manual import method, share the spreadsheet as well.

Do not forget to explain the expected result based on the sample data. Make sure that your explanation is referencing column names used in the sample data.

Regards,

Tom