cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jengwt 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
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors