Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Row based slicer

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's avatar
    Anonymous
    9 years ago

    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])
		))

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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's avatar
      Anonymous
      Not applicable
      9 years ago

      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's avatar
        Anonymous
        Not applicable
        9 years ago

        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