Calculated Table Filters

[czuniga]

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?

[bcdobbs · Accepted solution]

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?

[czuniga]

This worked. Thank you!

[bcdobbs]

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

[czuniga]

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

[bcdobbs]

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

[czuniga]

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. 

[bcdobbs]

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

[czuniga]

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