Want Excel filter functionality in Power Bi

Hi Experts,

I have a situation where i am not able to use any logic to get the desired result.

Scinario: If we open the attached excel file and put filter in Chargeable column for
below 30s, we will get the corresponding results for other column. Similarly, if i put filters on other columns, i get the result for others.

To achieve this excel functionality in powerbi, i have used SELECTEDVALUE and SWITCH function which is working fine.

Now i have put the columns Name, utilization filter, utilization2 in a table and tried

to filter below <30 and i can see Name ‘E’ and Utilization filter value and there are blank rows for corresponding values in Utilization2. I want see only < 30 value in Utilization filter column and corresponding values Utilization2 column

Note, if i put only Utilization filter and Name i get the correct result. But if i add Utilization 2 measure, i can’t see the desired result.

I have attached demo pbix file for your reference.

Any help is much appreciated.

Demo.pbix (38.4 KB)

Hello @nbaraili,

Thank you for posting your query onto the Forum.

So this happened because while applying the filter you’re supposed to calculate figure’s only against the selected values which were not blank and that why it was giving blank values persistently since that concept was ignored. Below is the screenshot provided for the reference.

But once you apply the context to ignore the blank values then you’ll achieve the desired result. Below is the screenshot provided for the reference where you’ll observe a minor change in formula which considers the context of ignoring blank values.

I’m also attaching the working of my PBIX file for the reference.

Hoping you find this useful and helps you to achieve the desired analysis.

Please feel free to write back in case I’ve missed out on anything.

Thanks & Warm Regards,
Harsh

Demo.pbix (38.5 KB)

1 Like

Hi @nbaraili, did the response provided by @Harsh help you solve your query? If not, how far did you get, and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

Hi Harsh,

Thank you very much.

The solution worked like a charm.

Indeed it solved my query.

Thank you.

Hello @nbaraili,

You’re welcome. :slightly_smiling_face:

I’m glad that I was able to help you.

Thanks & Warm Regards,
Harsh