Forum Discussion

Laflet's avatar
Laflet
Cloud Apps
07-04-2023

Referencing Filter Value in a Sisense Formula for Dynamic Data Display

I’m trying to switch the displayed data based on a user selected filter.

This would be done in Power BI using something like the below:

Value = SWITCH(TRUE(),

               [Selected Item] = “A” , Sum(A),

               [Selected Item] = “B” , Sum(B),

               [Selected Item] = “C” , Sum(A) / Sum(B)

)

I’d imagine if it’s possible in sisense it would utilize a case statement. Something like:

case

When  [Selected Item] = “A” then Sum(a)

When  [Selected Item] = “B” then Sum(b)

ELSE Sum(A) / Sum(B)

END

Can you reference the value of a filter in a value formula in sisense?

Code-first Analytics
dashboards & reporting

1 Reply

Replies have been turned off for this discussion
  • HamzaJ's avatar
    HamzaJ
    Data Integration
    07-04-2023

    Hey Laflet ,

    It is possible in a way with using measure-filters. I havent tried it out, but you could try the following

    case

    When  Sum(Selected Item) = (Sum(Selected Item) , A) then (Sum(Selected Item) , A)

    When  Sum(Selected Item) = (Sum(Selected Item) , B) then (Sum(Selected Item) , B)

    ELSE Sum(A) / Sum(B)

    END

    What this formula does is compare the Sum of the Selected Items with the Sum of the Selected items where filter = A. If it is equal then the SUM of A will be taken otherwise it will go down to the next condition. 

    Prerequisite is that the filter in (Sum(Selected Item) , A)  must be the same field as you are using on a dashboard level, otherwise this will not work. If it is the same it will work as follows:

    It will check the sum of the selected item filtered on the filters used on dashboard-level and compare this to the same sum but filtered specifically for value A (other dashboard filters will also be applied). If it is equal it means that the user selected A if not the user selected something else or selected all.

    More on measure-filters can be found here: https://docs.sisense.com/main/SisenseLinux/creating-formulas-based-on-criteria-and-conditions-filters.htm

     

    Hamza