Complex table relationships

[AnthonyMCUBI]

Hi everyone,

I'd like to create a measure in a table that has a relationship with a calculated date table. The date table is what we use to do all of our time-related filtering. When creating a visual that groups the measure by date, I'd like the grouping to be calculated instead of done by the relationship key. In particular, I'd like the measure to be grouped by date when it falls within a range between dates in the row, akin to Date1FromRow<= DateFromDateTable <= Date2FromRow, but it's currently Date1FromRow = DateFromDateTable, AKA a normal table relationship. Is that possible?

To word my question more generally, is it possible to create complex table relationships that are defined by DAX instead of a key? I figured it'd be possible as long as the DAX results in groups that obey the relationship rules (one-to-one, etc.).

As an example, this is the measure (Running Balance) grouped by a Date table column (Year Month).

The totals for Running Balance aren't what we want. They're currently by a relationship key between '*ACM Cylinder History' and 'Date Dimension', but what we want is, as mentioned above, a grouping by Date1 <= 'Date Dimension'[Date] <= Date2.

Any advice would be appreciated.

Thank you,

Anthony

[MFelix]

Hi @AnthonyMCUBI ,

Without a sample data and your sintax is difficult to givbe the exact answer but using the use of FILTER and ALL or the CROSSFILTER set to NONE will deactivate the use of the relationship.

Has you can see in the image below I have an active relationship but using the cross filter I was abble to make the sum being different from the current month:

Can you please share a mockup data or sample of your PBIX file and expected resutl. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

[AnthonyMCUBI]

Hi @MFelix ,

Here is the WeTransfer link to the example report: https://we.tl/t-psTz6NEKBz. It contains another attempt to create a Running Balance measure in the '*ACM Cylinder History' table; that method works but is unacceptably slow, so I'm still looking for a solution. I have another post on this forum about the performance of that implementation here if you're curious https://community.powerbi.com/t5/Desktop/Performance-Between-Two-Measures-How-to-make-the-slower-one....

Thank you.

[MFelix]

HI @AnthonyMCUBI ´,

Thak you for the file, but can you tell me what is the expected result that you are wainting for?

I have made the same visualization has you but not really sure about the final result.