Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |