Forum Discussion

I_miss_tableau's avatar
I_miss_tableau
Helper 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-based-on-numeric-paramater/m-p/3745167#M1216504

 

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. 

 

idRegionMeasure 1Measure 2Selected valueExclude
14325apac454490FALSE
436545emea56665590TRUE
55252apac345545690TRUE
25525amer12590FALSE
25552amer896690FALSE
552225emea4555690FALSE
5255225emea4514690TRUE
522552apac10067890TRUE
522552emea493390FALSE
2522emea1756690FALSE
25252amer23122490TRUE
252552emea67355690FALSE
522552amer 10133590TRUE
2552amer90323490FALSE

 

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. 

Need Help

4 Replies

  • 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
      Helper 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