Forum Discussion

czuniga's avatar
czuniga
Helper III
4 years ago
Solved

Calculated Table Filters

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?
all
calculated tables
Filters
  • bcdobbs's avatar
    bcdobbs
    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?

8 Replies

  • bcdobbs's avatar
    bcdobbs
    Community Champion
    4 years ago

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

    • czuniga's avatar
      czuniga
      Helper III
      4 years ago

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

      • bcdobbs's avatar
        bcdobbs
        Community Champion
        4 years ago

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

  • bcdobbs's avatar
    bcdobbs
    Community Champion
    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?