cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ChitraSamuel
Helper I
Helper I

Using What if parameters

The filter context seems to get altered while trying to use a what if parameter in a calculation

The data set for the below table is in a star schema format. A fact table linked to two dimension tables one being the employee dimension and another being the date dimension

ChitraSamuel_0-1655246789160.png

 

Why does the below measure using sum alter the filter context and introduce more rows? But the above sumx seems to be working correctly. What is happening with sum and sumx?

 

ChitraSamuel_1-1655246878077.png

 

 

7 REPLIES 7
ChitraSamuel
Helper I
Helper I

Thanks to all those who replied. I have solved my problem by creating a measure and using applying a filter

ChitraSamuel
Helper I
Helper I

How do i upload my pbix file?

lbendlin
Super User
Super User

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 assist you. I cannot use screenshots of your source data.

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

 

dim_employee_keyExcess LeaveLeave Liabilitytotal_ann_hourstotal_ann_daystotal_toil_hourstotal_toil_daystotal_rdo_hourstotal_rdo_daystotal_lsl_hourstotal_lsl_daystotal_hourstotal_days
1526899.88480595111.8848359.91349.643170.630.08689700451.251962.24163811.7948111.9717
1713846.0577792658.05778300.838839.584050000140.400318.47373441.239158.05778
16705165.0006797177.0007391.080853.942170000892.1742123.05851283.255177.0007
16516103.7715007115.7715173.171639.80956-5.00E-08-1.00E-0800330.434475.96194503.606115.7715
16704137.3544887149.3545443.842761.2196949.66.8413794.40.606897638.977388.13481136.82156.8028
1699895.66456501107.6646155.554821.455833.00E-081.00E-0800625.013386.20873780.5681107.6646
15288118.2670137130.267276.921236.4370000713.108193.83001990.0293130.267
1500848.6699023660.6699152.024820.968938.051.11034532.24.441379287.83239.70097480.106866.22163
16397118.9349114130.9349284.533439.245980000664.744791.68893949.2781130.9349
16260116.4790248128.479401.137355.329281.750.2413797.71.062069530.335673.14974940.9229129.7825
1687274.7697356486.76974468.040364.557281.60.2206900161.040322.21246630.680686.99043
1548191.7866822103.7867392.928151.701070000395.850752.08562788.7788103.7867
16728123.7947723135.7948382.094852.702730.750.1034480.10.013793602.417383.09205985.3621135.912
17038125.055147137.0551396.684854.7151440.55172428.1553.883448596.96582.340011025.805141.4903
14859182.8215086194.8215406.922153.542380.50.065789001073.721141.27911481.143194.8873
16957-10.915875581.0841247.2906580.95929700000.9486880.1248278.2393461.084124
16202158.1940327170.194453.981259.734370000839.4934110.45971293.475170.194
16373126.4305984138.4306196.522425.858210000855.5502112.57241052.073138.4306
16765171.66429183.6643439.804860.6627319.6252.7068973.4750.47931891.7613123.00161354.666186.8505
             

Above is a sample of the data

Excess leave and leave liability are measures

Leave Liability = sum(fact_daily_employee_leave_balances[total_ann_days]) + sum(fact_daily_employee_leave_balances[total_lsl_days])
 
Excess Leave = sumX(fact_daily_employee_leave_balances,fact_daily_employee_leave_balances[Leave Liability] - Parameter[Parameter Value])
 
Parameter is a what if parameter that helps sets the excess limit 
Has Excess? is a slicer that should filter the table based on the excess leave
If yes is selected it should filter all rows where excess leave is >0 and if no is selected it should filter all rows where the excess leave is <= 0
Below is the visualisation
 
ChitraSamuel_0-1655777285633.png

 

Can you help me with a solution

 

Thanks

Chitra

dim_employee_keyExcess LeaveLeave LiabilityLong Service LeaveRDOToilAnnual Leave
17443173.1684185.1684138.93880046.22962
16765171.6643183.6643123.00160.479312.70689760.66273
22584170.0329182.0329143.22125.5489660.6206938.81162
24838148.7087160.708787.1100303.72413873.59868
17228148.5594160.5594109.78220050.77721
16704137.3545149.354588.13480.6068976.84137961.21969
23911134.8182146.818268.791810.282759078.02642
17637133.8193145.8193102.92484.1586210.91034542.89445
16738131.3291143.3291111.80431.0441381.42813831.5248
22961130.1744142.1744105.38230036.79204
23192129.2997141.299783.1591-1.00E-08058.1406
24054109.4947121.494787.466280034.0284
17748107.8799119.879988.073951.1103450.03448331.80595
1772399.84228111.842377.46011.1428140.06896634.38218
2434088.48421100.484258.500087.1172414.44827641.98413
2399486.7015698.7015666.625130032.07643
2437184.0349396.0349357.958413.4689665.34482838.07652
1742982.5407694.5407639.76928-1.00E-080.23291954.77148
2483377.727189.727148.208356.9724143.91724141.51875
1672373.7263685.7263647.477430.00069038.24893
2362067.9162479.9162456.388980023.52725
2458566.2143978.2143953.6250500.06896624.58934
2346861.9354773.9354766.831452.2206907.104018
2341352.2427964.2427953.689082.1184210.05263210.55371
2347947.5095159.5095142.815280.075862016.69424
1671046.6555858.6555823.05634.2689665.48275935.59928
2558541.2498853.2498802.220690.68275953.24988
2305839.4547851.4547828.166070023.28872

 

Above is my sample data.

Table is fact_daily_employee_leave_balances

Excess Leave and Leave Liability are measures

Excess Leave = sumX(fact_daily_employee_leave_balances,fact_daily_employee_leave_balances[Leave Liability] - Parameter[Parameter Value])
Leave Liability = sum(fact_daily_employee_leave_balances[total_ann_days]) + sum(fact_daily_employee_leave_balances[total_lsl_days])
 
Parameter is a what if filter that allows the user to define the excess limit
Has Excess is a slicer that filters the table based on the excess leave
Yes means all rows that have excess leave >0 
No means all rows that have excess leave <= 0
Below is the visualisation
 

 

ChitraSamuel_0-1655768526514.png

 

Can somebody please help me with a solution

 

Thanks

Chitra

 

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors