topic Re: Calculated Table Filters in DAX Commands and Tips

Calculated Table Filters

czuniga — Mon, 13 Dec 2021 19:25:28 GMT

I'm working on creating calculated table that looks like this:

= SUMMARIZECOLUMNS(

CustomerID[Guarantor], CustomerID[Name], FILTER(Apts, Apts[AptDate] = TODAY()),

"# In Family ", Calculate(COUNT(CustomerID[PatNum]), ALL(CustomerID),

Clearing the filter with "ALL" for "# In Family" isn't working though. How can I do this?

Re: Calculated Table Filters

bcdobbs — Mon, 13 Dec 2021 19:49:34 GMT

Can you send a screen shot of your model (the relationship between CustomerID and Apts.

Re: Calculated Table Filters

czuniga — Mon, 13 Dec 2021 21:35:27 GMT

It's really sensitive data, but both CustomerID and Apts have the same CustomerID[PatNum] field and are related one to many

Re: Calculated Table Filters

bcdobbs — Mon, 13 Dec 2021 21:57:58 GMT

In what way is it not working? Is the number too high or too low?

Re: Calculated Table Filters

czuniga — Mon, 13 Dec 2021 22:08:31 GMT

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.

Re: Calculated Table Filters

bcdobbs — Mon, 13 Dec 2021 22:14:29 GMT

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]).

Re: Calculated Table Filters

czuniga — Mon, 13 Dec 2021 22:52:37 GMT

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])

Re: Calculated Table Filters

bcdobbs — Mon, 13 Dec 2021 23:02:50 GMT

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?

Re: Calculated Table Filters

czuniga — Tue, 14 Dec 2021 15:10:34 GMT

This worked. Thank you!