topic Re: How to filter 2 disconnected tables in DAX? in DAX Commands and Tips

How to filter 2 disconnected tables in DAX?

Anonymous — Wed, 11 Sep 2024 08:48:06 GMT

Hi friends,

MY issue I am having is 2 tables that used to be connected by a key column but it wasnt bringing the real numbers from current data table and the snapshot data table, I dont want to track the key column change, I want to compare total grouped by a category.

So, I decided they should be disconnected and I wanted to replicate the filters applied in the current data table and mirror to the snapshot data table, is there a way to do it?

To make it more clear, my poor decision to do it would be
CALCUALTE( COUNT [snapshot[data]] ), FILTER ( snapshot, snapshot[Category] = SELECTEDVALEU(current[Category])))

And I would try to replicate it in a measure for all possible filters, but it seems so so poor, pretty sure it must have a easier way to mirror all the possible filters.

Hope it gives a bit of context, thank you very much!

Re: How to filter 2 disconnected tables in DAX?

aymantm — Wed, 11 Sep 2024 09:16:10 GMT

Hi Gio.
I do not fully understand the inquiry, but I guess you should try connecting the tables via a bridge table that works as dimension.
It will be helpful to share the tables structure and the output needed.
PS: The expression you posted missspelled the "SELECTEDVALUE" function near the end.

Re: How to filter 2 disconnected tables in DAX?

Anonymous — Wed, 11 Sep 2024 10:17:00 GMT

Hi aymantm, currect I was using a bridge table but I have 5 to 10 columns that can be filtered out to narrow down our backlog, so it would need to use 10 bridge tables.

I was planning to use TREATAS to filter the columns but not sure if it would work, I will add them as a splicit filter if they applies.

My goal was to find a easier way to not make it explicit but something that can cover the whole table at once.

Hope I am not confusing more than helping.

Re: How to filter 2 disconnected tables in DAX?

Anonymous — Thu, 12 Sep 2024 08:24:12 GMT

Hi Anonymous

You can use the TREATAS() function to transfers a filter context from a table to another. This function is useful whenever a relationship does not exist, or when it cannot be created because the relationship is not a one-to-many, or because it is defined by two or more columns.

Here for your referece:
Propagating filters using TREATAS in DAX - SQLBI

TREATAS function - DAX | Microsoft Learn

Best Regards

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