cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Resolver I
Resolver I

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
Highlighted
Community Support
Community Support

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
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors