topic Re: Using What if parameters in Custom Visuals Development Discussion https://community.fabric.microsoft.com/t5/Custom-Visuals-Development/Using-What-if-parameters/m-p/2590757#M4613 <P><A href="https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216" target="_blank">How to provide sample data in the Power BI Forum - Microsoft Power BI Community</A></P> Tue, 21 Jun 2022 01:55:54 GMT lbendlin 2022-06-21T01:55:54Z Using What if parameters https://community.fabric.microsoft.com/t5/Custom-Visuals-Development/Using-What-if-parameters/m-p/2578727#M4590 <P>The filter context seems to get altered while trying to use a what if parameter in a calculation</P><P>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</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="ChitraSamuel_0-1655246789160.png" style="width: 858px;"><img src="https://community.fabric.microsoft.com/t5/image/serverpage/image-id/732322iE6F85149C255FEB4/image-dimensions/858x543?v=v2" width="858" height="543" role="button" title="ChitraSamuel_0-1655246789160.png" alt="ChitraSamuel_0-1655246789160.png" /></span></P><P>&nbsp;</P><P>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?</P><P>&nbsp;</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="ChitraSamuel_1-1655246878077.png" style="width: 928px;"><img src="https://community.fabric.microsoft.com/t5/image/serverpage/image-id/732323i5E1F6AD78794E438/image-dimensions/928x589?v=v2" width="928" height="589" role="button" title="ChitraSamuel_1-1655246878077.png" alt="ChitraSamuel_1-1655246878077.png" /></span></P><P>&nbsp;</P><P>&nbsp;</P> Tue, 14 Jun 2022 22:51:15 GMT https://community.fabric.microsoft.com/t5/Custom-Visuals-Development/Using-What-if-parameters/m-p/2578727#M4590 ChitraSamuel 2022-06-14T22:51:15Z Re: Using What if parameters https://community.fabric.microsoft.com/t5/Custom-Visuals-Development/Using-What-if-parameters/m-p/2581604#M4594 <P>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.<BR /><BR />Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.<BR /><BR /><A href="https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523" target="_blank">https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523</A></P> Thu, 16 Jun 2022 01:35:09 GMT https://community.fabric.microsoft.com/t5/Custom-Visuals-Development/Using-What-if-parameters/m-p/2581604#M4594 lbendlin 2022-06-16T01:35:09Z Re: Using What if parameters https://community.fabric.microsoft.com/t5/Custom-Visuals-Development/Using-What-if-parameters/m-p/2590585#M4608 <P>How do i upload my pbix file?</P> Mon, 20 Jun 2022 23:17:26 GMT https://community.fabric.microsoft.com/t5/Custom-Visuals-Development/Using-What-if-parameters/m-p/2590585#M4608 ChitraSamuel 2022-06-20T23:17:26Z Re: Using What if parameters https://community.fabric.microsoft.com/t5/Custom-Visuals-Development/Using-What-if-parameters/m-p/2590610#M4609 <TABLE border="0" cellspacing="0" cellpadding="0"><TBODY><TR><TD>dim_employee_key</TD><TD>Excess Leave</TD><TD>Leave Liability</TD><TD>Long Service Leave</TD><TD>RDO</TD><TD>Toil</TD><TD>Annual Leave</TD></TR><TR><TD>17443</TD><TD>173.1684</TD><TD>185.1684</TD><TD>138.9388</TD><TD>0</TD><TD>0</TD><TD>46.22962</TD></TR><TR><TD>16765</TD><TD>171.6643</TD><TD>183.6643</TD><TD>123.0016</TD><TD>0.47931</TD><TD>2.706897</TD><TD>60.66273</TD></TR><TR><TD>22584</TD><TD>170.0329</TD><TD>182.0329</TD><TD>143.2212</TD><TD>5.548966</TD><TD>0.62069</TD><TD>38.81162</TD></TR><TR><TD>24838</TD><TD>148.7087</TD><TD>160.7087</TD><TD>87.11003</TD><TD>0</TD><TD>3.724138</TD><TD>73.59868</TD></TR><TR><TD>17228</TD><TD>148.5594</TD><TD>160.5594</TD><TD>109.7822</TD><TD>0</TD><TD>0</TD><TD>50.77721</TD></TR><TR><TD>16704</TD><TD>137.3545</TD><TD>149.3545</TD><TD>88.1348</TD><TD>0.606897</TD><TD>6.841379</TD><TD>61.21969</TD></TR><TR><TD>23911</TD><TD>134.8182</TD><TD>146.8182</TD><TD>68.79181</TD><TD>0.282759</TD><TD>0</TD><TD>78.02642</TD></TR><TR><TD>17637</TD><TD>133.8193</TD><TD>145.8193</TD><TD>102.9248</TD><TD>4.158621</TD><TD>0.910345</TD><TD>42.89445</TD></TR><TR><TD>16738</TD><TD>131.3291</TD><TD>143.3291</TD><TD>111.8043</TD><TD>1.044138</TD><TD>1.428138</TD><TD>31.5248</TD></TR><TR><TD>22961</TD><TD>130.1744</TD><TD>142.1744</TD><TD>105.3823</TD><TD>0</TD><TD>0</TD><TD>36.79204</TD></TR><TR><TD>23192</TD><TD>129.2997</TD><TD>141.2997</TD><TD>83.1591</TD><TD>-1.00E-08</TD><TD>0</TD><TD>58.1406</TD></TR><TR><TD>24054</TD><TD>109.4947</TD><TD>121.4947</TD><TD>87.46628</TD><TD>0</TD><TD>0</TD><TD>34.0284</TD></TR><TR><TD>17748</TD><TD>107.8799</TD><TD>119.8799</TD><TD>88.07395</TD><TD>1.110345</TD><TD>0.034483</TD><TD>31.80595</TD></TR><TR><TD>17723</TD><TD>99.84228</TD><TD>111.8423</TD><TD>77.4601</TD><TD>1.142814</TD><TD>0.068966</TD><TD>34.38218</TD></TR><TR><TD>24340</TD><TD>88.48421</TD><TD>100.4842</TD><TD>58.50008</TD><TD>7.117241</TD><TD>4.448276</TD><TD>41.98413</TD></TR><TR><TD>23994</TD><TD>86.70156</TD><TD>98.70156</TD><TD>66.62513</TD><TD>0</TD><TD>0</TD><TD>32.07643</TD></TR><TR><TD>24371</TD><TD>84.03493</TD><TD>96.03493</TD><TD>57.95841</TD><TD>3.468966</TD><TD>5.344828</TD><TD>38.07652</TD></TR><TR><TD>17429</TD><TD>82.54076</TD><TD>94.54076</TD><TD>39.76928</TD><TD>-1.00E-08</TD><TD>0.232919</TD><TD>54.77148</TD></TR><TR><TD>24833</TD><TD>77.7271</TD><TD>89.7271</TD><TD>48.20835</TD><TD>6.972414</TD><TD>3.917241</TD><TD>41.51875</TD></TR><TR><TD>16723</TD><TD>73.72636</TD><TD>85.72636</TD><TD>47.47743</TD><TD>0.00069</TD><TD>0</TD><TD>38.24893</TD></TR><TR><TD>23620</TD><TD>67.91624</TD><TD>79.91624</TD><TD>56.38898</TD><TD>0</TD><TD>0</TD><TD>23.52725</TD></TR><TR><TD>24585</TD><TD>66.21439</TD><TD>78.21439</TD><TD>53.62505</TD><TD>0</TD><TD>0.068966</TD><TD>24.58934</TD></TR><TR><TD>23468</TD><TD>61.93547</TD><TD>73.93547</TD><TD>66.83145</TD><TD>2.22069</TD><TD>0</TD><TD>7.104018</TD></TR><TR><TD>23413</TD><TD>52.24279</TD><TD>64.24279</TD><TD>53.68908</TD><TD>2.118421</TD><TD>0.052632</TD><TD>10.55371</TD></TR><TR><TD>23479</TD><TD>47.50951</TD><TD>59.50951</TD><TD>42.81528</TD><TD>0.075862</TD><TD>0</TD><TD>16.69424</TD></TR><TR><TD>16710</TD><TD>46.65558</TD><TD>58.65558</TD><TD>23.0563</TD><TD>4.268966</TD><TD>5.482759</TD><TD>35.59928</TD></TR><TR><TD>25585</TD><TD>41.24988</TD><TD>53.24988</TD><TD>0</TD><TD>2.22069</TD><TD>0.682759</TD><TD>53.24988</TD></TR><TR><TD>23058</TD><TD>39.45478</TD><TD>51.45478</TD><TD>28.16607</TD><TD>0</TD><TD>0</TD><TD>23.28872</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>Above is my sample data.</P><P>Table is fact_daily_employee_leave_balances</P><P>Excess Leave and Leave Liability are measures</P><DIV><DIV><SPAN>Excess Leave = </SPAN><SPAN>sumX</SPAN><SPAN>(</SPAN><SPAN>fact_daily_employee_leave_balances</SPAN><SPAN>,</SPAN><SPAN>fact_daily_employee_leave_balances</SPAN><SPAN>[Leave Liability]</SPAN><SPAN> - </SPAN><SPAN>Parameter</SPAN><SPAN>[Parameter Value]</SPAN><SPAN>)</SPAN></DIV><DIV><DIV><DIV><SPAN>Leave Liability = </SPAN><SPAN>sum</SPAN><SPAN>(</SPAN><SPAN>fact_daily_employee_leave_balances[total_ann_days]</SPAN><SPAN>) + </SPAN><SPAN>sum</SPAN><SPAN>(</SPAN><SPAN>fact_daily_employee_leave_balances[total_lsl_days]</SPAN><SPAN>)</SPAN></DIV><DIV>&nbsp;</DIV><DIV><SPAN>Parameter is a what if filter that allows the user to define the excess limit</SPAN></DIV><DIV><SPAN>Has Excess is a slicer that filters the table based on the excess leave</SPAN></DIV><DIV><SPAN>Yes means all rows that have excess leave &gt;0&nbsp;</SPAN></DIV><DIV><SPAN>No means all rows that have excess leave &lt;= 0</SPAN></DIV><DIV><SPAN>Below is the visualisation</SPAN></DIV><DIV>&nbsp;</DIV></DIV></DIV></DIV><P>&nbsp;</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="ChitraSamuel_0-1655768526514.png" style="width: 647px;"><img src="https://community.fabric.microsoft.com/t5/image/serverpage/image-id/735323i7322E93B9F1261D6/image-dimensions/647x335?v=v2" width="647" height="335" role="button" title="ChitraSamuel_0-1655768526514.png" alt="ChitraSamuel_0-1655768526514.png" /></span></P><P>&nbsp;</P><P>Can somebody please help me with a solution</P><P>&nbsp;</P><P>Thanks</P><P>Chitra</P><P>&nbsp;</P> Mon, 20 Jun 2022 23:53:40 GMT https://community.fabric.microsoft.com/t5/Custom-Visuals-Development/Using-What-if-parameters/m-p/2590610#M4609 ChitraSamuel 2022-06-20T23:53:40Z Re: Using What if parameters https://community.fabric.microsoft.com/t5/Custom-Visuals-Development/Using-What-if-parameters/m-p/2590757#M4613 <P><A href="https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216" target="_blank">How to provide sample data in the Power BI Forum - Microsoft Power BI Community</A></P> Tue, 21 Jun 2022 01:55:54 GMT https://community.fabric.microsoft.com/t5/Custom-Visuals-Development/Using-What-if-parameters/m-p/2590757#M4613 lbendlin 2022-06-21T01:55:54Z Re: Using What if parameters https://community.fabric.microsoft.com/t5/Custom-Visuals-Development/Using-What-if-parameters/m-p/2590769#M4614 <P>&nbsp;</P><TABLE><TBODY><TR><TD>dim_employee_key</TD><TD>Excess Leave</TD><TD>Leave Liability</TD><TD>total_ann_hours</TD><TD>total_ann_days</TD><TD>total_toil_hours</TD><TD>total_toil_days</TD><TD>total_rdo_hours</TD><TD>total_rdo_days</TD><TD>total_lsl_hours</TD><TD>total_lsl_days</TD><TD>total_hours</TD><TD>total_days</TD></TR><TR><TD>15268</TD><TD>99.88480595</TD><TD>111.8848</TD><TD>359.913</TD><TD>49.64317</TD><TD>0.63</TD><TD>0.086897</TD><TD>0</TD><TD>0</TD><TD>451.2519</TD><TD>62.24163</TD><TD>811.7948</TD><TD>111.9717</TD></TR><TR><TD>17138</TD><TD>46.05777926</TD><TD>58.05778</TD><TD>300.8388</TD><TD>39.58405</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>140.4003</TD><TD>18.47373</TD><TD>441.2391</TD><TD>58.05778</TD></TR><TR><TD>16705</TD><TD>165.0006797</TD><TD>177.0007</TD><TD>391.0808</TD><TD>53.94217</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>892.1742</TD><TD>123.0585</TD><TD>1283.255</TD><TD>177.0007</TD></TR><TR><TD>16516</TD><TD>103.7715007</TD><TD>115.7715</TD><TD>173.1716</TD><TD>39.80956</TD><TD>-5.00E-08</TD><TD>-1.00E-08</TD><TD>0</TD><TD>0</TD><TD>330.4344</TD><TD>75.96194</TD><TD>503.606</TD><TD>115.7715</TD></TR><TR><TD>16704</TD><TD>137.3544887</TD><TD>149.3545</TD><TD>443.8427</TD><TD>61.21969</TD><TD>49.6</TD><TD>6.841379</TD><TD>4.4</TD><TD>0.606897</TD><TD>638.9773</TD><TD>88.1348</TD><TD>1136.82</TD><TD>156.8028</TD></TR><TR><TD>16998</TD><TD>95.66456501</TD><TD>107.6646</TD><TD>155.5548</TD><TD>21.45583</TD><TD>3.00E-08</TD><TD>1.00E-08</TD><TD>0</TD><TD>0</TD><TD>625.0133</TD><TD>86.20873</TD><TD>780.5681</TD><TD>107.6646</TD></TR><TR><TD>15288</TD><TD>118.2670137</TD><TD>130.267</TD><TD>276.9212</TD><TD>36.437</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>713.1081</TD><TD>93.83001</TD><TD>990.0293</TD><TD>130.267</TD></TR><TR><TD>15008</TD><TD>48.66990236</TD><TD>60.6699</TD><TD>152.0248</TD><TD>20.96893</TD><TD>8.05</TD><TD>1.110345</TD><TD>32.2</TD><TD>4.441379</TD><TD>287.832</TD><TD>39.70097</TD><TD>480.1068</TD><TD>66.22163</TD></TR><TR><TD>16397</TD><TD>118.9349114</TD><TD>130.9349</TD><TD>284.5334</TD><TD>39.24598</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>664.7447</TD><TD>91.68893</TD><TD>949.2781</TD><TD>130.9349</TD></TR><TR><TD>16260</TD><TD>116.4790248</TD><TD>128.479</TD><TD>401.1373</TD><TD>55.32928</TD><TD>1.75</TD><TD>0.241379</TD><TD>7.7</TD><TD>1.062069</TD><TD>530.3356</TD><TD>73.14974</TD><TD>940.9229</TD><TD>129.7825</TD></TR><TR><TD>16872</TD><TD>74.76973564</TD><TD>86.76974</TD><TD>468.0403</TD><TD>64.55728</TD><TD>1.6</TD><TD>0.22069</TD><TD>0</TD><TD>0</TD><TD>161.0403</TD><TD>22.21246</TD><TD>630.6806</TD><TD>86.99043</TD></TR><TR><TD>15481</TD><TD>91.7866822</TD><TD>103.7867</TD><TD>392.9281</TD><TD>51.70107</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>395.8507</TD><TD>52.08562</TD><TD>788.7788</TD><TD>103.7867</TD></TR><TR><TD>16728</TD><TD>123.7947723</TD><TD>135.7948</TD><TD>382.0948</TD><TD>52.70273</TD><TD>0.75</TD><TD>0.103448</TD><TD>0.1</TD><TD>0.013793</TD><TD>602.4173</TD><TD>83.09205</TD><TD>985.3621</TD><TD>135.912</TD></TR><TR><TD>17038</TD><TD>125.055147</TD><TD>137.0551</TD><TD>396.6848</TD><TD>54.71514</TD><TD>4</TD><TD>0.551724</TD><TD>28.155</TD><TD>3.883448</TD><TD>596.965</TD><TD>82.34001</TD><TD>1025.805</TD><TD>141.4903</TD></TR><TR><TD>14859</TD><TD>182.8215086</TD><TD>194.8215</TD><TD>406.9221</TD><TD>53.54238</TD><TD>0.5</TD><TD>0.065789</TD><TD>0</TD><TD>0</TD><TD>1073.721</TD><TD>141.2791</TD><TD>1481.143</TD><TD>194.8873</TD></TR><TR><TD>16957</TD><TD>-10.91587558</TD><TD>1.084124</TD><TD>7.290658</TD><TD>0.959297</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0.948688</TD><TD>0.124827</TD><TD>8.239346</TD><TD>1.084124</TD></TR><TR><TD>16202</TD><TD>158.1940327</TD><TD>170.194</TD><TD>453.9812</TD><TD>59.73437</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>839.4934</TD><TD>110.4597</TD><TD>1293.475</TD><TD>170.194</TD></TR><TR><TD>16373</TD><TD>126.4305984</TD><TD>138.4306</TD><TD>196.5224</TD><TD>25.85821</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>855.5502</TD><TD>112.5724</TD><TD>1052.073</TD><TD>138.4306</TD></TR><TR><TD>16765</TD><TD>171.66429</TD><TD>183.6643</TD><TD>439.8048</TD><TD>60.66273</TD><TD>19.625</TD><TD>2.706897</TD><TD>3.475</TD><TD>0.47931</TD><TD>891.7613</TD><TD>123.0016</TD><TD>1354.666</TD><TD>186.8505</TD></TR><TR><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR></TBODY></TABLE> Tue, 21 Jun 2022 02:00:43 GMT https://community.fabric.microsoft.com/t5/Custom-Visuals-Development/Using-What-if-parameters/m-p/2590769#M4614 ChitraSamuel 2022-06-21T02:00:43Z Re: Using What if parameters https://community.fabric.microsoft.com/t5/Custom-Visuals-Development/Using-What-if-parameters/m-p/2590782#M4615 <P>Above is a sample of the data</P><P>Excess leave and leave liability are measures</P><DIV><DIV><SPAN>Leave Liability = </SPAN><SPAN>sum</SPAN><SPAN>(</SPAN><SPAN>fact_daily_employee_leave_balances[total_ann_days]</SPAN><SPAN>) + </SPAN><SPAN>sum</SPAN><SPAN>(</SPAN><SPAN>fact_daily_employee_leave_balances[total_lsl_days]</SPAN><SPAN>)</SPAN></DIV><DIV>&nbsp;</DIV><DIV><DIV><DIV><SPAN>Excess Leave = </SPAN><SPAN>sumX</SPAN><SPAN>(</SPAN><SPAN>fact_daily_employee_leave_balances</SPAN><SPAN>,</SPAN><SPAN>fact_daily_employee_leave_balances</SPAN><SPAN>[Leave Liability]</SPAN><SPAN> - </SPAN><SPAN>Parameter</SPAN><SPAN>[Parameter Value]</SPAN><SPAN>)</SPAN></DIV><DIV>&nbsp;</DIV><DIV><SPAN>Parameter is a what if parameter that helps sets the excess limit&nbsp;</SPAN></DIV><DIV><SPAN>Has Excess? is a slicer that should filter the table based on the excess leave</SPAN></DIV><DIV><SPAN>If yes is selected it should filter all rows where excess leave is &gt;0 and if no is selected it should filter all rows where the excess leave is &lt;= 0</SPAN></DIV><DIV><SPAN>Below is the visualisation</SPAN></DIV><DIV>&nbsp;</DIV><DIV><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="ChitraSamuel_0-1655777285633.png" style="width: 712px;"><img src="https://community.fabric.microsoft.com/t5/image/serverpage/image-id/735361i0762AA1EBBEF0797/image-dimensions/712x349?v=v2" width="712" height="349" role="button" title="ChitraSamuel_0-1655777285633.png" alt="ChitraSamuel_0-1655777285633.png" /></span><P>&nbsp;</P><P>Can you help me with a solution</P><P>&nbsp;</P><P>Thanks</P><P>Chitra</P></DIV></DIV></DIV></DIV> Tue, 21 Jun 2022 02:08:46 GMT https://community.fabric.microsoft.com/t5/Custom-Visuals-Development/Using-What-if-parameters/m-p/2590782#M4615 ChitraSamuel 2022-06-21T02:08:46Z Re: Using What if parameters https://community.fabric.microsoft.com/t5/Custom-Visuals-Development/Using-What-if-parameters/m-p/2596040#M4621 <P>Thanks to all those who replied. I have solved my problem by creating a measure and using applying a filter</P> Thu, 23 Jun 2022 00:45:20 GMT https://community.fabric.microsoft.com/t5/Custom-Visuals-Development/Using-What-if-parameters/m-p/2596040#M4621 ChitraSamuel 2022-06-23T00:45:20Z