Reply
Member
Posts: 69
Registered: ‎02-13-2018

Create Calculated Column that Responds to Date Slicer

[ Edited ]

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

Highlighted
Community Support Team
Posts: 1,754
Registered: ‎03-15-2018

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