Forum Discussion
Toggle values of the table
Hi Everyone!
I am reaching out to seek assistance with implementing a slicer in Power BI. The objective is to create a slicer with values 0 and 1, where selecting 0 will hide entire rows where the sum of values (Amount + X + Y + Z + XZY) equals 0, and selecting 1 will display the entire table.
I have attached a snippet for your reference.
| Amount | X | Y | Z | XZY | |
| REVENUE | |||||
| GROSS SALES | 28686415.9 | 1.003 | 2177377 | 1.00299 | 203039.4 |
| SALES COMMISSION INCOME | 0 | 0 | 0 | 0 | 0 |
| RENT REVENUE | 0 | 0 | 0 | 0 | 0 |
| LESS: SALES RETURNS & ALLOWANCE/DISCOUNT | -7108.57 | -0.003 | -6483.87 | -0.00299 | -624.7 |
| NET SALES | 2373307.31 | 1 | 2170893 | 1 | 202414.7 |
Your expertise in resolving this issue would be highly appreciated.
Thank you in advance for your valuable assistance.
4 Replies
- ray_aramburo
Super User
I may suggest a different approach, if you have the measure already created for the sum of values = 0, great. Use that measure as a visual filter for when measure is not 0. Then, go to Bookmarks and create a Bookmark capturing the state of the table with the filter. Then, delete the filter and create a new Bookmark with this new state. Use the bookmark navigator button to show those 2 and that's it 🙂
Hi ray_aramburo, Thank you for the response. But we do have slicers like Year and Month affecting the table, In that case, would it be difficult to take this route of creating Bookmarks? Could you please suggest us a dynamic path for the same.
- ray_aramburo
Year and Month slicers are being used as a visual or as part of the Filters pane?
Thanks for the reply from ray_aramburo , please allow me to provide another insight:
Hi,ray_aramburoRegarding the issue you raised, my solution is as follows:
1.First I have created the following table and the column names and data are the data you have given:
2. Below are the measure I've created for your needs:
Measure = VAR SELCET=SELECTEDVALUE(CHOICE[Column1]) RETURN IF(SELCET=0,IF(CALCULATE(SUM('Table'[Column3]),FILTER(ALLSELECTED('Table'),'Table'[Column1]=MAX('Table'[Column1])))=0,1,0),1)3.Add mesure to the filtered view:
4.Here's my final result, which I hope meets your requirements.
Best Regards,
Leroy Lu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
