Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

How to get a summary table to act dynamically

Hi all. I have two tables in my PBI report, one named EA and another table named EA Summary, which as the title suggests, is a a summarized version of the 1st table.

 

I want EA Summary to act dynamically with EA so that when I filter EA by date the data in EA Summary changes accordingly. How do I go about accomplishing this? If it helps at all, below is my EA Summary table

EA Summary = summarize(EA , EA[PatientID] , EA[SubOrganizationId]

 

, "Met", MIN(EA[Numerator])
, "Denominator", MIN(EA[Denominator])
, "Visit Date", MIN(EA[VisitEndDate])
)

 

Thank you in advance.

Need Help

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    It can behave dynamically when

    1. data load happen

    2. It is used in a formula as var

     

    measure =

    var _table =

    summarize(EA , EA[PatientID] , EA[SubOrganizationId]

     

    , "Met", MIN(EA[Numerator])
    , "Denominator", MIN(EA[Denominator])
    , "Visit Date", MIN(EA[VisitEndDate])
    )

    return

    min([Visit Date])

  • v-lionel-msft's avatar
    v-lionel-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    Try to create relationship between EA and EA summary:

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi. I don't belive the relationship between the two tables would work in this scenario. The data in the EA Summary table would remain static, and would not change based on the filtering of dates/data. 

       

      For instance, lets say I have the following data in my EA table:

       

      PatientIDNumeratorVisitEndDate
      51Jan 1, 2020
      50Feb 1, 2020

       

      I want the Minimum Numerator by PatientID in the EA Summary table to change based of the filtered date range. Any tips on how I go about achieving this?

      • v-lionel-msft's avatar
        v-lionel-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi Anonymous ,

         

        Yes, in a one-to-many relationship, only one party can be used to filter multiple parties.

        You can create two measures instead of the calculated table:

        Met = 
CALCULATE(
    MIN(EA[Numerator]),
    ALLEXCEPT(
        EA,
        EA[PatientID]
    )
) 

Visite date = 
CALCULATE(
    MIN(EA[VisitEndDate]),
    ALLEXCEPT(
        EA,
        EA[PatientID]
    )
)

         

        Best regards,
        Lionel Chen

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.