Advanced filter vs Basic filter issue when ALL used in measure

[Sunnyatwork]

Hello guys, I am facing an weird issue related to Advance filter vs basic filter.  When we select Advance filter (For example Starts with A), the measure is displaying correct results ignoring Practice Name filter. When we select Basic filter and select all the practice names starting with letter 'A' using check boxes, it is not ignoring practice name filter. You can see results below in the screenshot. Particularly row # 3, where it is not ignoring the practice name filter.

I have created a Measure with below calculation:

Benchmark = CALCULATE(AVERAGE('prv ProviderMeasures'[benchmarkScore]), ALL('prv lk_AffiliationNames'[practiceName]))

Data model:

I have two tables, ProviderMeasures & lk_AffiliationNames with Many - Many relationship and cross filter direction set to BOTH. Scores are coming from ProviderMeasures, Practice name is coming from affiliation table. Filters are coming from both tables but for this Benchmark measure I need to ignore only Practice Name. 

I have applied visual level filter here, but it is doing same for page level filters.

[v-zhenbw-msft]

Hi @Sunnyatwork ,

How about the result after you follow the suggestions mentioned in my original post?
Could you please provide more details or expected result about it If it doesn't meet your requirement?

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-zhenbw-msft]

Hi @Sunnyatwork ,

Sorry for that it is difficult for us to reproduce your issue, maybe you can try ALLEXCEPT function like this,

Benchmark =
CALCULATE (
    AVERAGE ( 'prv ProviderMeasures'[benchmarkScore] ),
    ALLEXCEPT ( 'prv lk_AffiliationNames', 'prv lk_AffiliationNames'[CPID] )
)

Or you can filter the not the practice name value in your measure,

Benchmark =
CALCULATE (
    AVERAGE ( 'prv ProviderMeasures'[benchmarkScore] ),
    FILTER (
        ALL ( 'prv lk_AffiliationNames'[practiceName] ),
        'prv lk_AffiliationNames'[practiceName] <> "practice name"
    )
)

If it doesn’t meet your requirement, could you please provide a mockup sample based on fake data or describe the fields of each tables and the relations between tables simply?

It will be helpful if you can show us the exact expected result based on the tables.

Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Sunnyatwork]

Thank you for your replies @v-zhenbw-msft  and @amitchandak 

Hope you guys see my image now  

@v-zhenbw-msft I am doing similar. I have value which aggregates normally and I have Benchmark like your measure which shouldn't change with filter  

My Benchmark shouldn't change with filter. I am using ALL for this purpose but they are changing when I use Basic filtering.

@amitchandak wouldn't work for me as I am comparing 2 measures in same visual, one should change with filter and other shouldn't. 

Hope I made it clear. 

Hope you guys can see my screenshot, May be my organization security is stopping from uploading. 

I have 3 tables. One without filtering being applied, one with 'Starts with' and 3rd one is with Basic filtering option. 

As you may see, 1st two options result same value, but Basic filtering has different result due to some records are getting filtered out. For example, 3rd row. 

[v-zhenbw-msft]

Hi @Sunnyatwork ,

Sorry for that we cannot see your screenshots, please re-upload using Jpg format.

We create a sample, and what is your desire result in the following screenshots?

If it doesn't meet your requirement, could you please show the exact expected result based on the table that we have shared?

BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k]

@Sunnyatwork Image is not available on your post, you should be using ALLSELECTED instead of ALL

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[Sunnyatwork]

@parry2k Thank your reply. I am not sure how to upload an image into the post, I was using insert picture, but my png file is not getting inserted.

ALLSELECTED is giving me what I selected, My expectation is, I do NOT want to apply filter at all, My measure should completely ignore the filter I am applying

[amitchandak]

@Sunnyatwork , if all and all selected is not serving the purpose then check if edit interactions can help you

https://docs.microsoft.com/en-us/power-bi/service-reports-visual-interactions