Forum Discussion

I_miss_tableau's avatar
I_miss_tableau
Icon for Helper I rankHelper I
2 years ago

Exclude rows based on number paramater

Hi,  I tried to exclude rows which exclude rows based on user selection and received great help on this forum. Link below:   https://community.fabric.microsoft.com/t5/Desktop/Exclude-outliers-base...
Need Help
Anonymous's avatar
Anonymous
Not applicable
2 years ago

HI I_miss_tableau,

You can try to use following measure formula to check records and return flag, then you can use it on 'visual level filter' to filter records:

flag =
VAR condit =
    MAX ( 'Table1'[Selected value] )
VAR filtered =
    CALCULATETABLE (
        'Table1',
        ALLSELECTED ( 'Table1' ),
        VALUES ( 'Table1'[id] ),
        VALUES ( 'Table1'[Region] )
    )
VAR summary =
    SUMMARIZE (
        filtered,
        'Table1'[id],
        'Table1'[Region],
        "Max_Rate", MAX ( [M1], [M3] )
    )
RETURN
    IF ( COUNTROWS ( FILTER ( summary, [Max_Rate] > condit ) ) > 0, "Y", "N" )

Regards,

Xiaoxin Sheng

I_miss_tableau's avatar
I_miss_tableau
Icon for Helper I rankHelper I
2 years ago

Thank you but this table was only an example because I must not share my work data. My data source contains about 20 measures and 100 dimensions, do I need to include all of them in your calculation?

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi I_miss_tableau,

    For the fields use as category in summarize function, you only need the field that uses current visual as axis.

    In addition, did these measure expressions need to be checked? If that's the case, you can modify above formula to use list operator and iterator aggregate function Maxx to handle them.

    flag =
VAR condit =
    MAX ( 'NewTable'[Selected value] )
VAR filtered =
    CALCULATETABLE (
        'Table1',
        ALLSELECTED ( 'Table1' ),
        VALUES ( 'Table1'[id] ),
        VALUES ( 'Table1'[Region] )
    )
VAR summary =
    SUMMARIZE (
        filtered,
        'Table1'[id],
        'Table1'[Region],
        "Max_Rate", MAXX ( { [M1], [M2], [M3], [M4], [M5] }, [Value] )
    )
RETURN
    IF ( COUNTROWS ( FILTER ( summary, [Max_Rate] > condit ) ) > 0, "Y", "N" )

    Regards,

    Xiaoxin Sheng

    • I_miss_tableau's avatar
      I_miss_tableau
      Icon for Helper I rankHelper I
      2 years ago

      What do you mean by checking measures? I use different measures and I would need differetn aggregation for them. My excldusion will be based only on first measures so I want to exclude only rows which exceed selected value in M1. It doesnt matter what it is in m2,m3,m4 - rows with m1 over number paramater will be excluded.