The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now
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:
The fields of importance in the Phonecalls table are:
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
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
User | Count |
---|---|
161 | |
111 | |
96 | |
87 | |
75 |
User | Count |
---|---|
158 | |
136 | |
133 | |
81 | |
61 |