Forum Discussion
Calculated Table Filters
- 4 years ago
Decided to build a demo... Is this reflective of your setup?
Basic Apts Data:
Basic CustomerId Data:
With this setup if I create a calculated table with your code:
NewTable = SUMMARIZECOLUMNS( CustomerID[Gaurantor], CustomerID[Name], FILTER(Apts, Apts[Date] = TODAY()), "# In Family ", CALCULATE( COUNT(CustomerID[PatNum]), ALL(CustomerId) ) )It gives back
which is a count of every row in the CustomerId table.
I wondered if you have a bidirectional relationship in your model? As if I switch it to that I get only count for that day.
Also are you definently wanting to count the number of id's in CustomerId summarised by Gauarntor and Name? Won't that always be 1? Do you actually want the number of related Ids in the Apt table with the date filter removed? In which case:
NewTable = SUMMARIZECOLUMNS( CustomerID[Gaurantor], CustomerID[Name], FILTER(Apts, Apts[Date] = TODAY()), "# In Family ", CALCULATE( COUNT(Apts[PatNum]), REMOVEFILTERS( Apts[Date] ) ) )If I'm totally missing the point can you illustrate with a very simple demo pbix file or download and modify Sample File and mock up expected output in excel?
It's really sensitive data, but both CustomerID and Apts have the same CustomerID[PatNum] field and are related one to many
In what way is it not working? Is the number too high or too low?
- czuniga4 years agoHelper III
The other columns have the filter applied and therefore the number is too low. I want the filter to apply to just the CustomerID[Guarantor], CustomerID[Name] columns and aggregate the rest without being limited to the date filter.
- bcdobbs4 years agoCommunity Champion
I think I might have to build an example for myself in morning. Can't quite picture it!
What happens if instead of ALL(CustomerId) you try ALL(Apts[AptDate]).- czuniga4 years agoHelper III
I thought that could be the solution! Unfortunately, it didn't change anything. I don't get an error message, but it's the exact same result as not having ALL(Apts[AptDate])