DAX FILTER() like Page Level Filter on Dimension Table

[Anonymous]

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.

[v-shex-msft]

HI @Anonymous,

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

[Anonymous]

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.

[v-shex-msft]

Hi @Anonymous,

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

[Anonymous]

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.

[v-shex-msft]

HI @Anonymous,

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