Forum Discussion
Solved
Calculated column do not react on page filter
Hello all, I'm trying to create a calculated column that groups the diff of each rows due date and a variable deadline (page filter). I have a data set like this: invoice number due date cust...
Hi Louke ,
My steps are as follows:
1. Create a calendar table with all the dates needed and apply it as field of the slicer. Select Before for the slicer type.
2. Enter data -> Table2:
3. New measures:
filter = VAR _deadline = MAX ( 'Calendar'[Date] ) VAR _days = DATEDIFF ( MAX ( 'Table'[due date] ), _deadline, DAY ) VAR _filter = IF ( _days <= 40, 1, 0 ) RETURN _filterRevenue = VAR _less_than_40 = SUMX ( FILTER ( 'Table', [filter] = 1 ), 'Table'[revenue] ) VAR _more_than_40 = SUMX ( FILTER ( 'Table', [filter] = 0 ), 'Table'[revenue] ) VAR _result = SWITCH ( SELECTEDVALUE ( 'Table2'[datediff] ), "< 40 days", _less_than_40, "> 40 days", _more_than_40 ) RETURN _result4. Result:
You can change the values in the slicer at any time and the table visual will change dynamically. The PBIX file is attached for reference.
Best Regards,
changqing
changqing
Resolver II
Resolver IIHi Louke ,
My steps are as follows:
1. Create a calendar table with all the dates needed and apply it as field of the slicer. Select Before for the slicer type.
2. Enter data -> Table2:
3. New measures:
filter =
VAR _deadline =
MAX ( 'Calendar'[Date] )
VAR _days =
DATEDIFF ( MAX ( 'Table'[due date] ), _deadline, DAY )
VAR _filter =
IF ( _days <= 40, 1, 0 )
RETURN
_filterRevenue =
VAR _less_than_40 =
SUMX ( FILTER ( 'Table', [filter] = 1 ), 'Table'[revenue] )
VAR _more_than_40 =
SUMX ( FILTER ( 'Table', [filter] = 0 ), 'Table'[revenue] )
VAR _result =
SWITCH (
SELECTEDVALUE ( 'Table2'[datediff] ),
"< 40 days", _less_than_40,
"> 40 days", _more_than_40
)
RETURN
_result4. Result:
You can change the values in the slicer at any time and the table visual will change dynamically. The PBIX file is attached for reference.
Best Regards,
changqing