Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Partial filtering with a integer-based slicer visual

Hello,

 

I have the following simplified data:

State AState B
400600
450 
  
300450

 

I want to count the amount of rows in each state which are satisfied based on user input (a slicer). The user input is a threshold, above which all rows are not accepted. Let's say i set the slicer to 500.

 

In the first row, the values are 400 and 600 for state A and B, respectively. This means that the row is in state A, but not state B. In the fourth row, the values are 300 and 450. This means that the row is in state B.

 

My complete expected outcome would the following:

StateAmount
A2
B1
In neither state1

 

Let's say i slice based on the integer in the "State B"-column. I set the slicer to 500, but this filters out the entire row, even though state A is still satisfied.

 

I have tried many different methods but have not been able to figure out a solution. 

Need Help
Tutorial Requests
  • v-xiaotang's avatar
    v-xiaotang
    4 years ago

    Hi Anonymous 

    Thanks for your reply.

     

    The previous solution is based on this 

    Based on the new expected result, you can try this measure

    State(dynamic) = 
    var _slicer=SELECTEDVALUE(Slicer[Parameter])
    var _stateA=SELECTEDVALUE('Table'[State A])
    var _stateB=SELECTEDVALUE('Table'[State B])

return 
    SWITCH(TRUE(),    
    ISBLANK(_stateA)&&ISBLANK(_stateB) || _slicer<_stateA,"In neither state",
    (_slicer>=_stateA && _slicer<_stateB ) || (_slicer>=_stateA&&ISBLANK(_stateB)),"A",
    _slicer>=_stateB,"B"
    )

    result

     

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

6 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    This will be easier to solve if you unpivot your data table.

    State Amount
    A 400
    B 600
    A 450
    A 300
    B 450

     

    Is that possible in your case?

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks for the suggestion!

       

      Unfortunately, it is not possible. Each row has a unique ID - the following table more accurately depicts the situation.

      IDState AState B
      A1400600
      A2450 
      A3  
      B1300450

       

      Furthermore, the unique ID and all the other columns in my actual data needs to be preserved.

  • v-xiaotang's avatar
    v-xiaotang
    Community Support
    4 years ago

    Hi Anonymous 

    Thanks for reaching out to us.

     

    Is column [state A] smaller than [state B]? If so, you can try this measure,

    Measure = 
    var _slicer=SELECTEDVALUE(Slicer[Parameter])
    var _a=CALCULATE(COUNT('Table'[State A]),FILTER('Table',('Table'[State A]<=_slicer&&'Table'[State B]>_slicer) || ('Table'[State A]<=_slicer&&ISBLANK('Table'[State B]))))
    var _b=CALCULATE(COUNT('Table'[State B]),FILTER('Table','Table'[State B]<=_slicer))
    var _n=CALCULATE(COUNTROWS('Table'),FILTER('Table',(ISBLANK('Table'[State A])&& ISBLANK('Table'[State B])) || 'Table'[State A]>_slicer))
return 
    SWITCH(TRUE(),
    MAX(Outcome[State])="A",_a,
    MAX(Outcome[State])="B",_b,
    _n)

     

    if you need more help, please @ me .

     

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hello again,

      Yes, State A will always be equal to or smaller than State B.

       

      Your suggestion is almost right. I however still want to be able to visualize the data contained in all the other columns and not just a summation of the number of rows fulfilling the if statement.

       

      I have looked into the DATATABLE function, but it does not support dynamically editing the values of a column, each time a new slicer value is selected by the user.

       

      The following table is my wanted result with slicer = 700:

      IDState (dynamic)Property 1 (static)Property 2 (static)
      A1Bxyz1xyz1
      A2

      A

      		xyz2xyz2
      A3In neitherxyz3xyz3
      B1Bxyz4xyz4

       

      The following table is my wanted result with slicer = 350:

      IDState (dynamic)Property 1 (static)Property 2 (static)
      A1In neither statexyz1xyz1
      A2In neither statexyz2xyz2
      A3In neither statexyz3xyz3
      B1Axyz4xyz4

       

      Is this result achievable? 

      Edit: It would also be acceptable to generate three lists containing the ID's in a given state. So e.g. {State A: ["A2"], State B: ["A1", "B1"], In neither state: ["A3"]}.

      • v-xiaotang's avatar
        v-xiaotang
        Community Support
        4 years ago

        Hi Anonymous 

        Thanks for your reply.

         

        The previous solution is based on this 

        Based on the new expected result, you can try this measure

        State(dynamic) = 
    var _slicer=SELECTEDVALUE(Slicer[Parameter])
    var _stateA=SELECTEDVALUE('Table'[State A])
    var _stateB=SELECTEDVALUE('Table'[State B])

return 
    SWITCH(TRUE(),    
    ISBLANK(_stateA)&&ISBLANK(_stateB) || _slicer<_stateA,"In neither state",
    (_slicer>=_stateA && _slicer<_stateB ) || (_slicer>=_stateA&&ISBLANK(_stateB)),"A",
    _slicer>=_stateB,"B"
    )

        result

         

        Best Regards,

        Community Support Team _Tang

        If this post helps, please consider Accept it as the solution to help the other members find it more quickly.