cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jengwt Member
Member

Create Calculated Column that Responds to Date Slicer

Hello,

 

I need some help creating a calculated column that dynamically takes a date slicer into account.

 

I have two tables, one a list of customers, the other a list of calls made to those customers.

The fields of importance in the Customers table are:

  • CustID [unique]

The fields of importance in the Phonecalls table are:

  • CallID [unique]
  • CustID
  • CallDate

 

I need to make a new column in the customers table that will count the number of calls made to a given customer within the dates specified by the user with a Call Date slicer.

 

The problem is that the customers table does not recognize the date filtering implimented by the call date slicer.

 

Could I do this with a Measure? Yes. However, I NEED to be able to do this in a column for reasons that are not relevant here.

Thank you for your help!

 

 

-----------------------------------------------------------------------------------------------------------------------------------

 

 

This is the, "No, Microsoft, I'm not an idiot," section of the post.

 

This is the best solution that I've attempted so far:

 

Measures:

Max Date = MAX('Phonecalls'[CallDate])

Min Date = MIN('Phonecalls'[CallDate])

 

These two measures correctly read the first and last dates with relation to the call date slicer.

 

Column:

Calls = CALCULATE(COUNT('Phonecalls'[CallID])
    , FILTER('Phonecalls'
        , AND('Customers'[CustID] = 'Phonecalls'[CustID]
            , AND( [Max Date] >= 'Phonecalls'[CallDate]
                , [Min Date] <= 'Phonecalls'[CallDate]
                )
            )

        )
    )

 

Unfortunately the column still does not respect the Max and Min Dates; it seems to ignore them and revert to the literal max and min call dates in the Phonecalls dataset.

 

I also tried this, same results:

Calls = COUNTX(FILTER('Phonecalls'
        , AND('Customers'[CustID] = 'Phonecalls'[CustID]
            , AND( [Max Date] >= 'Phonecalls'[CallDate]
                , [Min Date] <= 'Phonecalls'[CallDate]
                )
            )
        )

    , 'Phonecalls'[CallID]

    )

 

As proof, adding the following column to the Customers table yields all "No"s:
question = IF(ISFILTERED('Phonecalls'[CallDate]), "Yes", "No")

But the following Measure correctly returns "Yes"s:

question2 = IF(ISFILTERED('Phonecalls'[CallDate]), "Yes", "No")

 

If you must have more info on the problem, please refer to: http://community.powerbi.com/t5/Desktop/get-Pie-Chart-to-Filter-Table/m-p/433758

1 REPLY 1
Community Support Team
Community Support Team

Re: Create Calculated Column that Responds to Date Slicer

Hello @jengwt

Thank you for providing such detailed information to help me learn about the problem.

However, I'm afraid the following requirement can't be achieved by creating a calculated column since a calculated column would not change with selection in a slicer.

"a new column in the customers table that will count the number of calls made to a given customer within the dates specified by the user with a Call Date slicer"

 

Best Regards

Maggie 

 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 473 members 5,417 guests
Please welcome our newest community members: