Exclude rows based on number paramater

[I_miss_tableau]

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-based-on-numeric-paramater/m-p/37...

Hovewer I don't want exclude outliers from only one measure but remove them completely in filters and calculate another measure without these rows. For now, I know how to calculate average excluding outliers. 

id	Region	Measure 1	Measure 2	Selected value	Exclude
14325	apac	45	44	90	FALSE
436545	emea	566	655	90	TRUE
55252	apac	345	5456	90	TRUE
25525	amer	12	5	90	FALSE
25552	amer	89	66	90	FALSE
552225	emea	45	556	90	FALSE
5255225	emea	451	46	90	TRUE
522552	apac	100	678	90	TRUE
522552	emea	49	33	90	FALSE
2522	emea	17	566	90	FALSE
25252	amer	231	224	90	TRUE
252552	emea	67	3556	90	FALSE
522552	amer	101	335	90	TRUE
2552	amer	90	3234	90	FALSE

I need to create column TRUE/FALSE to exclude outliers for measure 1 (ids which are greated then 90). I dont want to create excluding formulas for all measures in the chart I will create. 

[v-shex-msft]

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]

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?

[v-shex-msft]

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]

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.