filter from many to one relation between two tables

[jayjay0306]

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

[Shaurya · Accepted solution]

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: Forecast Period - Previous Forecasts

[jayjay0306]

Thanks Shauya, this works perfectly. 🙂

Br,

Jayjay0306

[johnt75]

Try changing it to

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