Reply
Member
Posts: 134
Registered: ‎08-03-2016

DAX FILTER() like Page Level Filter on Dimension Table

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.

Community Support Team
Posts: 7,505
Registered: ‎08-14-2016

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

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
Member
Posts: 134
Registered: ‎08-03-2016

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

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] /* I CAN'T DO THIS BECAUSE 'FACT'[COD] IS NOT IN SUMMARIZE*/ IN ALLSELECTED('DimTable'[COD])
    )
)

Any tips?

 

Thanks again.

Highlighted
Community Support Team
Posts: 7,505
Registered: ‎08-14-2016

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

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
Member
Posts: 134
Registered: ‎08-03-2016

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

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:

 

Untitled.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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.

Community Support Team
Posts: 7,505
Registered: ‎08-14-2016

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

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |