Here is the sample scenario where I want to increase the budget to 20% if there is filter from country and 10% increase if am selected both country & Function , Any help on how can I achieve this by DAX logic?
Here is the sample data set.
ID
Country
Sales Year
Type
Budget
Function
E1
India
2020
Retail
250
HR
E2
USA
2022
Wholesale
341
Finance
E3
China
2020
Retail
56
HR
E4
India
2022
Wholesale
976
IT
E5
USA
2022
Retail
567
IT
E6
India
2020
Wholesale
1001
IT
E7
USA
2021
Retail
896
Finance
E8
USA
2021
Wholesale
1500
IT
E9
India
2020
Retail
211
IT
E10
USA
2020
Wholesale
578
HR
E11
Brazil
2021
Retail
234
HR
E12
Brazil
2022
Retail
976
IT
When there is a filter(Slicer) on only Country like for example USA I want the budget to be increase by 20%
Type
Sum of Budget
Percent Increase
Retail
1463
1755.6
Wholesale
2419
2902.8
When there is slicer of Country - USA and another slicer i.e function - HR then budget should be increased by 10%.
In order to achieve the results based on the condition that you’ve specified. First we’ll convert the flat file into the proper data model. Below is the screenshot of the data model provided for the reference -
As you suggested that, country will have “single select filter” but in case there’re multi select filters for function then you can replace the “HASONEFILTER()” function with the “ISFILTERED()” function. So that multiple filter selections are taken into consideration. Below is the revised version of the measure provided for the reference -
Increased Budgets =
VAR _Selected_Country =
SELECTEDVALUE( Country[Country] )
VAR _Selected_Function =
SELECTEDVALUE( Function[Function] )
VAR _Results =
IF(
ISFILTERED( Function[Function] ) && ISFILTERED( Country[Country] ) ,
[Total Actual Budgets] + ( [Total Actual Budgets] * 0.1 ) ,
IF(
ISFILTERED( Country[Country] ) ,
[Total Actual Budgets] + ( [Total Actual Budgets] * 0.2 ) ,
[Total Actual Budgets] ) )
RETURN
_Results
The reason I used one table initially because I want my function slicer to be updated based on country filter like you see here when I filter country - “India” I also see Finance function in filter which should not appear ideally because there is no “finance” function under country India.
Is there a possibility where the function slicer to have drop down of values only based on the selection we make in country filter ?
It’s not a big deal. You can do that by putting a measure inside a filter pane by selecting the Function slicer and put the condition as “Total Budgets > 0”. Below is the screenshot provided for the reference -
Once you do this, now you’ll see only “HR” and “IT” as a result for “India”. Below is the screenshot provided for the reference -
The reason why I converted the flat file into a data model is because firstly, it’s a recommended best practice and secondly, it’s helpful in the case of advanced or complex calculations. One such example was witnessed recently onto the forum where flat file had to be converted into a proper data model to achieve the results. Below is the link of that post provided for the reference.
It’s your choice whether to go with the flat file or with the proper data model.
I’m also attaching the working PBIX file for the reference purposes.
