Forum Discussion
Positive and negative filter
- Anonymous4 years ago
Hi jvandyck ,
I updated your sample pbix file(see attachment), please check if that is what you want.
1. Create two dimension table use the field [Salary Code] of table Sheet1
Positive codes = VALUES('Sheet1'[Salary Code])Negative codes = VALUES('Sheet1'[Salary Code])2. Apply the fields in above dimension tables on the slicers(Positive and Negative)
3. Create a measure as below to judge if the enterprise should display or not
Measure = VAR _selent = SELECTEDVALUE ( 'Sheet1'[Enterprise] ) VAR _positivecodes = ALLSELECTED ( 'Positive codes'[Salary Code] ) VAR _negativecodes = ALLSELECTED ( 'Negative codes'[Salary Code] ) VAR _count1 = CALCULATE ( DISTINCTCOUNT ( 'Sheet1'[Enterprise] ), FILTER ( 'Sheet1', 'Sheet1'[Enterprise] = _selent && 'Sheet1'[Flag] = 1 && 'Sheet1'[Salary Code] = SELECTEDVALUE ( 'Negative codes'[Salary Code] ) ) ) VAR _tab1 = CALCULATETABLE ( VALUES ( 'Sheet1'[Enterprise] ), FILTER ( 'Sheet1', 'Sheet1'[Enterprise] = _selent && 'Sheet1'[Flag] = 1 && IF ( ISFILTERED ( 'Positive codes'[Salary Code] ), 'Sheet1'[Salary Code] IN _positivecodes, 1 = 1 ) ) ) VAR _tab2 = CALCULATETABLE ( VALUES ( 'Sheet1'[Enterprise] ), FILTER ( 'Sheet1', 'Sheet1'[Enterprise] = _selent && 'Sheet1'[Flag] = 1 && IF ( ISFILTERED ( 'Negative codes'[Salary Code] ), 'Sheet1'[Salary Code] IN _negativecodes, 1 = 1 ) ) ) RETURN IF ( NOT ( ISFILTERED ( 'Negative codes'[Salary Code] ) ), IF ( _selent IN _tab1, 1, 0 ), IF ( _selent IN EXCEPT ( _tab1, _tab2 ), 1, 0 ) )4. Create a table visual with visual-level filter condition(Measure is 1)
Best Regards
jvandyck , You can create two measures and create calculation groups
or a new column
if([salary] <0, "Negative", "Positive")
- jvandyck4 years agoHelper IV
thank you for your swift reply...but how can I then add 2 listbars, listing all salary codes, one as the positive filter and one as the negative filter to select the codes I want to use in my positive and negative filter?
- amitchandak4 years agoSuper User
jvandyck , If this does not help
Can you share sample data and sample output in table format?But I think calculation group can help
Calculation Groups- Measure Slicer, Measure Header Grouping, Measure to dimension conversion. Complex Table display: https://youtu.be/qMNv67P8Go0