Forum Discussion
jvandyck
4 years agoHelper IV
Positive and negative filter
I have a table with 2 fields listing company id and salary code id to indicate which company uses which salary codes. This is a big tabel with over 230million rows. I want to add a positive and a ne...
- 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