cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper III

## filter from many to one relation between two tables

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

1 ACCEPTED SOLUTION
Memorable Member

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

3 REPLIES 3
Memorable Member

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

Helper III

Thanks Shauya, this works perfectly. 🙂

Br,

Jayjay0306

Super User

Try changing it to

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

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors