How to get a summary table to act dynamically

[bireportsHG]

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.

[v-lionel-msft]

Hi @bireportsHG ,

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.

[bireportsHG]

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:

PatientID	Numerator	VisitEndDate
5	1	Jan 1, 2020
5	0	Feb 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]

Hi @bireportsHG ,

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.

[amitchandak]

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