Forum Discussion
Exclude Select ALL option for a field in a calculation
Hello,
I am trying to create a calculated column and have to exclude Select All option from the logic for that specific field. I have targets only for individual values and not all, meaning when all values are selected in the filter, the target should be blank. Please note that i cannot use single select option as i have other calculations which gets impacted.
Please see details below:
| Region | Country | Target | Reg A- target is only at country level for AA & AC countries | ||
| A | AA | 20 | Reg B- target is only at country level for BB country | ||
| A | AB | Reg C-target is at region level | |||
| A | AC | 30 | |||
| B | BA | ||||
| B | BB | 50 | If Region A is selected and all countries selected, target should be blank | ||
| B | BC | If Region A is selected and either AA or AC countries selected, target should be as mentioned | |||
| C | CB | If Region A is selected and AB country is selected, then Target should be blank | |||
| C | CA | 60 | |||
| C | CC | 60 | I have to create a calculation for Target based on specified criteria and then compare Target vs actuals |
You cant do that with a calculated column as it isn't aware of any slicer selections. Its value will not change no matter what you select in the slicer.
Hi anamika_singh ,
Thank you for reaching out to the Microsoft Fabric Community. Also thank you danextian and lbendlin for your inputs.
This scenario can be handled using a DAX measure instead of a calculated column. In Power BI, when all values in a slicer are selected, it behaves like no filter is applied. To exclude the "Select All" case, you can create a measure that compares the number of selected countries with the total countries in the selected region. If all are selected, the measure returns blank; otherwise, it returns the appropriate target value.
This ensures your targets display only when specific countries are selected, without impacting other calculations.
If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.
Thankyou.
Hi anamika_singh ,
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
6 Replies
- lbendlin
Super User
Please provide a more detailed explanation of what you are aiming to achieve. What have you tried and where are you stuck?
- danextian
You cant do that with a calculated column as it isn't aware of any slicer selections. Its value will not change no matter what you select in the slicer.
Hi anamika_singh ,
Thank you for reaching out to the Microsoft Fabric Community. Also thank you danextian and lbendlin for your inputs.
This scenario can be handled using a DAX measure instead of a calculated column. In Power BI, when all values in a slicer are selected, it behaves like no filter is applied. To exclude the "Select All" case, you can create a measure that compares the number of selected countries with the total countries in the selected region. If all are selected, the measure returns blank; otherwise, it returns the appropriate target value.
This ensures your targets display only when specific countries are selected, without impacting other calculations.
If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.
Thankyou.
Hi anamika_singh ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.
Hi @anamika_singh ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.Hi anamika_singh ,
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
