topic Re: Change color icons based on the slicer selection in DAX Commands and Tips

Change color icons based on the slicer selection

Anonymous — Tue, 05 Nov 2024 05:46:26 GMT

Hi Experts,

I am working on the requirements below to showcase the individual data when we select any options in slicer.

(ACCOUNT)	(OFFER TYPE)	(SCOPE)	(SCOPE COMBINED)	(FORECAST)	(REVENUE)	(CM%)
AAA	X	RED	RED	445354	454367	35%
AAA	Y	GREEN	RED	8676	9764	35%
BBB	Y	AMBER	AMBER	123445	976765	35%
BBB	X	GREEN	AMBER	33555	87654	35%

above the sample data , in that SCOPE and SCOPE COMBINED columns are calculated columns which we have developed already to display the risks in color icons .
We have a OFFER TYPE slicer in the page and In the slicer we have offer types (eg : X , Y , etc) When slicer OFFER TYPE is "ALL" then we can show combined row as below :

Acc	Scope Comb.	forecast	revenue	CM%
AAA	RED	454030 (sum of both AAA account forecast values)	464131	35%
BBB	AMBER	.....	.....	35%

but when we select some selection from the slicer (X or Y) then it should show only specific respective row data .
Example : when i select "X" in the slicer , the table should show only X related data in the table expected as below : (removed OFFER TYPE & SCOPE columns)

ACCOUNT	SCOPE COMBINED	FORECAST	REVENUE	CM%
AAA	RED	445354	454367	35%
BBB	GREEN	33555	87654	35%

Scope combined DAX we created :

Scope Combined =

VAR A = 'Test Delivery Updates'[Account Name]

VAR B = ADDCOLUMNS(SUMMARIZE(FILTER('Test Delivery Updates','Test Delivery Updates'[Account Name]=A),'Test Delivery Updates'[Scope logic]),"Scope",SWITCH('Test Delivery Updates'[Scope logic],"Red",3,"Amber",2,"Green",1,0))

RETURN MAXX(TOPN(1,B,[Scope]),'Test Delivery Updates'[Scope logic])

So far the combined rows are working fine when OFFER TYPE is "ALL" , so we want to fix the single offer type selection from the slicer.

Hope it is clear .. let me know if require more data.

Thanks

Re: Change color icons based on the slicer selection

Ankur04 — Tue, 05 Nov 2024 06:43:50 GMT

Hi Anonymous ,

could you please explain in detials with your raw data and expected output. I tried to understand from the above query your calculation by default should work.

Thanks,

Ankur

Re: Change color icons based on the slicer selection

Anonymous — Tue, 05 Nov 2024 07:36:40 GMT

Ankur04

Offer type and scope logic columns are not required in this table , i just added for the understanding.
when we select offer type options in the slicer then the scope logic icon (green) should display in the scope combined column , this is the expected result .

Re: Change color icons based on the slicer selection

Ankur04 — Tue, 05 Nov 2024 09:50:10 GMT

Hi Anonymous ,

can you try something like this.

Scope_Combine = CALCULATE(MAX('Table (9)'[(SCOPE)]),'Table (9)'[(OFFER TYPE)] = SELECTEDVALUE('Table (9)'[(OFFER TYPE)]))

you can modify it based on some condition.if this is not your expectation then please help me to understand more about your requirement.

Thanks,

Ankur

Re: Change color icons based on the slicer selection

Anonymous — Tue, 05 Nov 2024 14:34:40 GMT

Ankur04 Sorry i don't understand this .. Can you please explain what you are trying to say ?

Re: Change color icons based on the slicer selection

Anonymous — Wed, 06 Nov 2024 02:14:15 GMT

Hi ALL
Firstly Ankur04 thank you for your solution!

And Anonymous ,You want to show the value of the scope combine in the case of multi-selection, and the value of the scope in the case of single-selection, right?
If that's the case, we can create a new metric to determine whether we're multi-selecting or single-selecting to determine how we need to format the output.

Dynamic Scope = IF( ISFILTERED('Test Delivery Updates'[Offer Type]) && HASONEVALUE('Test Delivery Updates'[Offer Type]), MAX('Test Delivery Updates'[Scope]), MAX('Test Delivery Updates'[Scope Combined]) )

If you still have questions, check out my pbix file, I hope my solution helps, I would be honored if I could solve your problem!

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

Re: Change color icons based on the slicer selection

Anonymous — Wed, 06 Nov 2024 08:46:21 GMT

Anonymous Thanks a lot for your solution 🙂

This is the expectation from my end 🙂