topic Re: filter from many to one relation between two tables in DAX Commands and Tips

filter from many to one relation between two tables

jayjay0306 — Fri, 14 Oct 2022 12:39:40 GMT

Hi guys,

I hope you can help me with a problem:

I have a DIM_Product table and a FACT_Volume table.

The relation between the "product"-table and "Volume"-table is '1-to-many'.

In my Product table I have a lot of redundant products, which have no volume (in the example above there is one ("3")).

My problem is that I can't make the cross-filter direction="both", which would solve the problem.

Now, my question is:

Is there a way to set up a calculated column (fx. "flag") on the product table, which can be used as filter, based on the volumes in the volume-table?

I have tried the following as a calculated column on the product table:

Active products = IF(NOT(ISEMPTY('Volume table')),1,0)

... but that didn't work.

Any ideas?

thanks.

Br,

Jayjay0306

Re: filter from many to one relation between two tables

johnt75 — Fri, 14 Oct 2022 16:16:56 GMT

Try changing it to

Active products = IF(NOT(ISEMPTY(RELATEDTABLE('Volume table'))),1,0)

Re: filter from many to one relation between two tables

Shaurya — Fri, 14 Oct 2022 16:37:21 GMT

Hi jayjay0306,

You can use:

Flag = IF(CALCULATE(SUM(Volume[Volume]),FILTER(Volume,'Product'[ID]=Volume[ID]))>0,1,0)

Works for you? Mark this post as a solution if it does!
Consider taking a look at my blog:

Re: filter from many to one relation between two tables

jayjay0306 — Mon, 17 Oct 2022 08:54:01 GMT

Thanks Shauya, this works perfectly. 🙂

Br,

Jayjay0306