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 sli...
  • 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])
		))
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

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      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's avatar
        Anonymous
        Not applicable
        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])
		))