- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

# DAX FILTER() like Page Level Filter on Dimension Table

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-16-2018 04:20 AM

Hi guys.

Simple (I assume) doubt.

I have something like this:

MEASURE =

CALCULATE (

SUM ( 'Table1'[Column1] );

FILTER (

SUMMARIZE (

ALL (

'Table1'[Column2];

'Table1'[Column3]

);

'Table1'[Column2];

'Table1'[Column3];

"MEASURE1"; SUM ( 'Table1'[Column4] )

);

[MEASURE1] >= 200

)

)

And I have the report page filtered with one Dimension Table so the ID on that Dim Table is not blank.

How can I add this to the DAX?

Something like:

(...) ); [MEASURE1] >= 200 && 'Table1'[Column2] IN VALUES('DimTable'[ID]) ) )

I tried this but it just doesn't work.

Thanks in advance.

## Re: DAX FILTER() like Page Level Filter on Dimension Table

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-16-2018 11:20 PM

HI @svalen,

You can try to use following measure if it works on your side:

MEASURE = CALCULATE ( SUM ( [Column1] ); FILTER ( SUMMARIZE ( ALL ( 'Table1' ); 'Table1'[Column2]; 'Table1'[Column3]; "MEASURE1"; SUM ( 'Table1'[Column4] ) ); [MEASURE1] >= 200 && [Column2] IN ALLSELECTED ( 'DimTable'[ID] ) ) )

If above not help, please share some sample data to test and coding formula.

Regards,

Xiaoxin Sheng

If this post

**helps**, then please consider

**to help the other members find it more quickly.**

*Accept it as the solution*For learning resources/Release notes, please visit: | |

## Re: DAX FILTER() like Page Level Filter on Dimension Table

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-17-2018 02:24 AM

Thanks @v-shex-msft.

Well that looks very good but now I see I have another problem. The column to filter is not in the SUMMARIZE. And I don't want to add it, because it would break the [MEASURE1] > 200 part, as it would group further and I don't need it,

To be clearer, I'll share what I have and what I can't add now:

RAPSI REAL = CALCULATE ( SUM ( 'FACT'[RAPSI] ); FILTER ( SUMMARIZE ( ALL ( 'FACT'[ENTIDADE_ID]; 'FACT'[MonthYear_FACT] ); 'FACT'[ENTIDADE_ID]; 'FACT'[MonthYear_FACT]; "MEASURESIVALUEPF"; SUM ( 'FACT'[SI VALUE PF] ) ); [MEASURESIVALUEPF] >= 200 && 'FACT'[COD]IN ALLSELECTED('DimTable'[COD]) ) )/* I CAN'T DO THIS BECAUSE 'FACT'[COD] IS NOT IN SUMMARIZE*/

Any tips?

Thanks again.

## Re: DAX FILTER() like Page Level Filter on Dimension Table

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-23-2018 12:09 AM

Hi @svalen,

I'd like to suggest you move this condition to above to filter table before summarize:

RAPSI REAL = CALCULATE ( SUM ( 'FACT'[RAPSI] ); FILTER ( SUMMARIZE (FILTER ( ALL ( 'FACT' ); 'FACT'[COD] IN ALLSELECTED ( 'DimTable'[COD] ) ); [ENTIDADE_ID]; [MonthYear_FACT]; "MEASURESIVALUEPF"; SUM ( 'FACT'[SI VALUE PF] ) ); [MEASURESIVALUEPF] >= 200 ) )

Regards,

Xiaoxin Sheng

If this post

**helps**, then please consider

**to help the other members find it more quickly.**

*Accept it as the solution*For learning resources/Release notes, please visit: | |

## Re: DAX FILTER() like Page Level Filter on Dimension Table

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-23-2018 02:46 AM

Hi and thanks for your reply.

Let's take a step back and try a simpler example, as I changed the formulas to a cleaner version:

RAPSI = VAR RAP = [RAPCIN] //SUMX on FACT table VAR SIVALUE = CALCULATE ( [SI VALUE PF]; ALL ( Dim[COD] ) ) // sumx of value and qty over all the codes RETURN IF ( SIVALUE >= 200;CALCULATE ( RAP; FILTER ( ALL ( 'FACT' ); 'FACT'[COD] IN ALLSELECTED ( 'FilterTable'[COD] ) ) )//this is what you added)

And this doesn't work either, I'm checking and unchecking the Page Filters and the values still change.

What am I doing wrong?

Thanks for your help.

## Re: DAX FILTER() like Page Level Filter on Dimension Table

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-24-2018 02:59 AM

HI @svalen,

Do you mean nested measures to calculate them in one measure? If this is a case, I'd like sample data to test.(measure which has specific filters not fully works on nested formula)

__Optimizing DAX expressions involving multiple measures__

You can also try to use below formula if you not nested measures:

RAPSI = IF ( CALCULATE ( [SI VALUE PF]; ALL ( Dim[COD] ) ) >= 200; SUMX ( FILTER ( ALL ( 'FACT' ); 'FACT'[COD] IN ALLSELECTED ( 'FilterTable'[COD] ) ); [RAPCIN] ) )

Regards,

Xiaoxin Sheng

If this post

**helps**, then please consider

**to help the other members find it more quickly.**

*Accept it as the solution*For learning resources/Release notes, please visit: | |