Partial filtering with a integer-based slicer visual

[Anonymous]

Hello,

I have the following simplified data:

State A	State B
400	600
450
300	450

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:

State	Amount
A	2
B	1
In neither state	1

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. 

[v-xiaotang]

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]

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:

ID	State (dynamic)	Property 1 (static)	Property 2 (static)
A1	B	xyz1	xyz1
A2	A	xyz2	xyz2
A3	In neither	xyz3	xyz3
B1	B	xyz4	xyz4

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

ID	State (dynamic)	Property 1 (static)	Property 2 (static)
A1	In neither state	xyz1	xyz1
A2	In neither state	xyz2	xyz2
A3	In neither state	xyz3	xyz3
B1	A	xyz4	xyz4

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 · Accepted solution]

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.

[Anonymous]

This is exactly what I needed, thanks for the support!

[AlexisOlson]

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]

Thanks for the suggestion!

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

ID	State A	State B
A1	400	600
A2	450
A3
B1	300	450

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