Filter table visual in a report by a measure

[redko_f]

Hello Guys!

I'm quite new to Power BI and I'm seeking help with one issue which I have.

I have a dashboard with a table visual, it displays the amount of the samples received by person and region.
I need to filter this visual in a way that will be displayed only the names of people who received samples above the threshold.

I tried to use the IF statement for the evaluation of this column but once put into table visual it doesn't display the right data.
And I need to be sure the table will be dynamically filtered on the base of page filters (time and country).
To define the threshold I use a measure that dynamically calculates it on the basis of Statistical distribution.
Here is formula for threshold measure i take data from fact table, I group it by user IT and then calculate both deviation and Mean on base of the ourput dataset:

Standard Deviation + Mean HCPs =

Var sum_sampl = ADDCOLUMNS(SUMMARIZE(call2_vod,call2_vod[AccountId]),"Number of samples" ,Calculate(sum(call2_sample_vod__c[Quantity_vod__c]), recordtype[isHCP] = 1, recordtype[isActive] = "true", account[isInactive] = "false", call2_vod[IsSampleVisit] = "true"))

Return

STDEVX.P(sum_sampl,[Number of samples])*2 + AVERAGEX(sum_sampl,[Number of samples])

Here is a table visual about which I talk its super simple:

Thank you for your help guys!

[v-jingzhang · Accepted solution]

Hi @redko_f 

You could add ALL() to surround the call2_vod table in SUMMARIZE statement. Then use this measure in an IF statement to compare with the personal value. Use the IF measure to play as a filter in the table's visual-level filter. This can remain the page filters. 

Standard Deviation + Mean HCPs =
VAR sum_sampl =
    ADDCOLUMNS (
        SUMMARIZE ( ALL ( call2_vod ), call2_vod[AccountId] ),
        "Number of samples",
            CALCULATE (
                SUM ( call2_sample_vod__c[Quantity_vod__c] ),
                recordtype[isHCP] = 1,
                recordtype[isActive] = "true",
                account[isInactive] = "false",
                call2_vod[IsSampleVisit] = "true"
            )
    )
RETURN
    STDEVX.P ( sum_sampl, [Number of samples] ) * 2
        + AVERAGEX ( sum_sampl, [Number of samples] )

In the following measure, I assume [Number of Samples] is a measure to get personal values. Use this measure to filter the table and set to show items when the value is 1. 

Flag = IF ( [Number of Samples] > [Standard Deviation + Mean HCPs], 1, 0 )

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[lbendlin]

Page filters are the right approach for that. 

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to work with. Avoid posting screenshots of your source data if possible.

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523