cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Frequent Visitor

## 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 customer revenue 500001 11.06.2022 Tom 100,00 € 500002 11.06.2022 Jack 50,00 € 500003 17.06.2022 Isabell -50,00 € 500004 18.06.2022 Tom -250,00 € 500005 26.06.2022 Harry 50,00 € 500006 30.06.2022 Jack 100,00 € 500007 30.06.2022 Frank -200,00 € 500008 30.06.2022 Tom 150,00 € 500009 03.07.2022 Isabell 50,00 €

I need a variable page filter that could be switched by the user:

Now, I would like to add a calculated column for the datediff between due date and deadline date (in the example I set 31.07.2022 - but it should be possible to change it):

 invoice number due date customer revenue datediff 500001 11.06.2022 Tom 100,00 € -50 500002 11.06.2022 Jack 50,00 € -50 500003 17.06.2022 Isabell -50,00 € -44 500004 18.06.2022 Tom 250,00 € -43 500005 26.06.2022 Harry 50,00 € -35 500006 30.06.2022 Jack 100,00 € -31 500007 30.06.2022 Frank 200,00 € -31 500008 30.06.2022 Tom 150,00 € -31 500009 03.07.2022 Isabell 50,00 € -28

Result:

 datediff revenue > 40 days 350,00 € < 40 days 550,00 €

As I already read, page filters can not affect context in calculated columns. But is there a way I can keep the variable deadline filter and achieve the desired result?

I already tried different ways like:

``````datediff =
IF( DATEDIFF(MIN(Time[Deadline]), Table[due date], DAY) < -40,
"> 40 days",
"< 40 days")``````

Unfortunately PBI ignores my page filter for "Deadline".

Thank you so much in advance!

1 ACCEPTED SOLUTION
Resolver II

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

Best Regards,
changqing

2 REPLIES 2
Resolver II

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

Best Regards,
changqing

Super User

@Louke , the Calculated column, does not react to slicer or filter. Only measure can.

Create a measure like

datediff = Maxx( Table,
IF( DATEDIFF(MIN(Time[Deadline]), Table[due date], DAY) < -40,
"> 40 days",
"< 40 days") )

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors