Toggle values of the table

[Analystdata]

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.

[v-linyulu-msft · Accepted solution]

Thanks for the reply from @ray_aramburo , please allow me to provide another insight:
Hi,@ray_aramburo 

Regarding 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.

[Analystdata]

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?

[ray_aramburo]

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 🙂