Row based slicer

[Anonymous]

Hey guys,

I have a small problem and I'm not sure weather its possible to do it in PowerBI or not (it's easy in Excel though).

So there are 3 rows "EBIT, OPEX and Order Entry" that can be sliced/filtered on "EX_DT" or "INTS" (external/internal). HOWEVER, EBIT should NOT be affected of the two slicers and should always be the total.. I mean, act like EX_DT and INTS are selected all the time or be somehow disconnected to the slicer, while the other 2 rows should react to the slicer.

Is it somehow possible to achieve?

[Anonymous]

Hi @Anonymous,

For your requirement, you can add a condition to calculate with current row content.

Measure sample:

Calculate=
var currKPI=LASTNONBLANK(Table[KPI],[KPI])
Return
IF(currKPI<>"EBIT", calculate(sum(Table[VALUE]),ALLSELECT(Table)),calculate(sum(Table[VALUE]),ALL(Table)))

Regards,

Xiaoxin Sheng

[Anonymous]

Thank you so much, but it doesn't seem to work. Unfortunately, I'm still learning DAX and have only basic understanding and can't figure out where the error is..

[Anonymous]

Hi @Anonymous,

You can put these formula and test again.

According to your screenshot, it seems not support ',', I'd like to suggest you use ';' character to replace ',' .

Calculate=
var currKPI=LASTNONBLANK(Table[KPI];[KPI])
Return
IF(currKPI<>"EBIT"; calculate(sum(Table[VALUE]);ALLSELECT(Table));calculate(sum(Table[VALUE]);ALL(Table)))

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @Anonymous

it still doesn't work. I had to change "ALLSELECT" to "ALLSELECTED", since it didn't recognize ALLSELECT as a function. But it still doesn't work. Any further ideas?

 

Thanks in advance and best regards.

[Anonymous · Accepted solution]

A friendy coleague just helped me out, we used following measure to achieve what I was looking for, since your solution didn't work out:

SumValue = IF(HASONEVALUE('Table'[KPI]);
			SWITCH(VALUES('Table'[KPI]);
			"EBIT";CALCULATE(SUM('Table'[VALUE]);ALL('Table'[EX_DT_IN]));SUM('Table'[VALUE])
		))

[Shruti_0527]

Hi,

How to add slicers in row headers matrix visual in power bi.

[Greg_Deckler]

Use a CALCULATE with an ALL filter.