topic Re: Cross-Filtering a Visual Based on an Inactive Relationship in DAX Commands and Tips

Cross-Filtering a Visual Based on an Inactive Relationship

Paul_Rockliffe — Tue, 04 Jul 2023 13:14:51 GMT

I have an ative relationship between a date table and a dimension table based on the processing date and an inactive relationship based on the due date.

I've created a Measure that allows me to count my dimensions based on the inactive relationship (Due Date) and put this into a column chart so that I can see how many things were due on each date. Alongside this visual I have a table visual that contains all my dimensions, with the Name column droped in so I have a list of all my things.

I want to be able to select one of my bars and have it filter the list of things to only the things that were due on that date. Because the cross-filtering uses the active relationship, what I'm given is the list of things that were processed on that date instead.

Is there a way to get this to work that doesn't involve using Power Query to duplicate my dimension table so I can have a different active relationship on my second copy of the table?

Re: Cross-Filtering a Visual Based on an Inactive Relationship

Anonymous — Tue, 11 Jul 2023 06:53:15 GMT

HI Paul_Rockliffe,

Do you mean you have multiple inactivated relationships need to be switched based on the selections to dynamic affect the calculation results?

If that is the case, I'd like to suggest you setting and what if parameter table with all type of relationship names. Then you can write measure formula with switch function to return different calculation formulas based on current selection.
Regards,

Xiaoxin Sheng

Re: Cross-Filtering a Visual Based on an Inactive Relationship

Paul_Rockliffe — Tue, 11 Jul 2023 08:00:10 GMT

Thanks, yes that might work too.

I ended up finding a similar question and adapting the answer. My solution isn't perfect because I couldn't find a way to do this that didn't use CALCULATE with an Aggregation, so the result is always a single Row. But by putting another column from my table into the Table visual it then returns a single Row for every Row, which removes the aggregation in practice. This is fine for this one, as I wanted a couple of columns anyway.

Measure is:

Selected Measures by Due Date =

CALCULATE(

MAXX('Live Commission Dashboard',

'Live Commission Dashboard'[Status]),

ALLSELECTED('Month Calendar'[Date]),

USERELATIONSHIP('Month Calendar'[Date], 'Live Commission Dashboard'[Due Date])

)

Re: Cross-Filtering a Visual Based on an Inactive Relationship

Anonymous — Fri, 14 Jul 2023 09:23:02 GMT

HI Paul_Rockliffe,

AFAIK, current measure expression not able to use as axis or category to expand the aggregate records. You can only add more category fields to expand aggregated row contexts.
Regards,

Xiaoxin Sheng