Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Show element even when it is filtered out

Hello everyone,   I've got a matrix in Power BI representing the sum of values for some elements. Within some of the elements there are calculations, for example:  To achieve this representa...
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous ,

    I just updated your sample pbix file, you can get it from this link. Later please check if that is what you want. Any comment or problem later, please feel free to let me know.

    1. Create a table with unique value of Column1 in table Groups

    2. Create a slicer with the field "Column1" in new table

    3. Update the formula of measure "Value2" as below:

    Value2 = 
    var _selcol1=ALLSELECTED('Table'[Column1])
    VAR CalcABC = CALCULATE(SUM(Data[Value]), FILTER(ALL('Groups'), Groups[Column1] in _selcol1 &&'Groups'[Column1]<>"D")) 
    VAR CalcABCD = CALCULATE(SUM(Data[Value]), FILTER(ALL('Groups'), Groups[Column1] in _selcol1 ))
    return 
     SWITCH(
         TRUE(),
         CALCULATE(SEARCH("SUM A+B+C+D", SELECTEDVALUE(Groups[Column1]),1,0), ALL(Calendar[Date])) > 0, CalcABCD,
         CALCULATE(SEARCH("SUM A+B+C", SELECTEDVALUE(Groups[Column1]),1,0), ALL(Calendar[Date])) > 0, CalcABC,
        CALCULATE(sum('Data'[Value]),FILTER('Groups','Groups'[Column1] in _selcol1 || SEARCH("SUM",'Groups'[Column1],1,0)>0)))

    Best Regards

    Rena