Forum Discussion

Louke's avatar
Louke
Frequent Visitor
4 years ago
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...
Need Help
  • changqing's avatar
    changqing
    4 years ago

    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
    _filter
    Revenue = 
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
    _result

    4. 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.

    PBIX file 

     

    Best Regards,
    changqing

changqing's avatar
changqing
Resolver II
4 years ago

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
    _filter
Revenue = 
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
    _result

4. 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.

PBIX file 

 

Best Regards,
changqing