Change color icons based on the slicer selection

[dinesharivalaga]

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

DK

[v-xingshen-msft · Accepted solution]

Hi ALL
Firstly  Ankur04 thank you for your solution!

And @dinesharivalaga ,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.

[dinesharivalaga]

@v-xingshen-msft  Thanks a lot for your solution 🙂

This is the expectation from my end 🙂

[Ankur04]

Hi @dinesharivalaga ,

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

[dinesharivalaga]

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

[Ankur04]

Hi @dinesharivalaga ,

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

[dinesharivalaga]

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