Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
Louke
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 numberdue datecustomerrevenue
50000111.06.2022Tom      100,00 €
50000211.06.2022Jack       50,00 €
50000317.06.2022Isabell       -50,00 €
50000418.06.2022Tom      -250,00 €
50000526.06.2022Harry       50,00 €
50000630.06.2022Jack      100,00 €
50000730.06.2022Frank      -200,00 €
50000830.06.2022Tom      150,00 €
50000903.07.2022Isabell       50,00 €

 

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

Louke_0-1659515139946.png

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 numberdue datecustomerrevenuedatediff
50000111.06.2022Tom      100,00 €-50
50000211.06.2022Jack       50,00 €-50
50000317.06.2022Isabell       -50,00 €-44
50000418.06.2022Tom      250,00 €-43
50000526.06.2022Harry       50,00 €-35
50000630.06.2022Jack      100,00 €-31
50000730.06.2022Frank      200,00 €-31
50000830.06.2022Tom      150,00 €-31
50000903.07.2022Isabell       50,00 €-28

 

Result:

datediff
revenue
> 40 days350,00 €
< 40 days550,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
changqing
Resolver II
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.

changqing_0-1660029698855.png

2. Enter data -> Table2:

changqing_1-1660029809565.png

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:

changqing_2-1660029922722.png

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

View solution in original post

2 REPLIES 2
changqing
Resolver II
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.

changqing_0-1660029698855.png

2. Enter data -> Table2:

changqing_1-1660029809565.png

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:

changqing_2-1660029922722.png

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

amitchandak
Super User
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") )

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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