Find min date within a group in a heirarchy

[kza40381]

Hi All,

I have a union query which includes study, country and site level data and milestones.  I am using a matrix to display the heirarchal view of the data.  Therefore, you would have a study row, then a row for each Region beneath, then the sites for the Region, beneath Region.

I need to create 4 columns, with logic, which result in a 1 if:

• the site date is less than the region date for a patricular study
• the site date is less than the study date for the study
• if the min date of all the regions of a study is earlier than the study level milestone
• a column that results in a 1 if the study has a site or region date which is less than the study level date. 

Below is an example of what I am looking for.  I hope you can help me.

[lbendlin]

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to work with. Avoid posting screenshots of your source data if possible.

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

[kza40381]

Thank you for your response.  Is this helpful?

Heirarchy level	Study #	Country	Site	FSI	Site FSI < Region FSI	Site FSI < Study FSI	Min Region < Study	Study has either Region or Site FSI  which is < Study FSI
Study	1			8/15/2018				1
Region	1	Israel		3/29/2019	0		0	0
Site	1	Israel	9721	10/31/2018	0	0	0	0
Site	1	Israel	9722	11/13/2018	0		0	0
Site	1	Israel	9723	(Blank)	0	0	0	0
Region	1	Italy		1/1/2017	0	0	1	0
Site	1	Italy	3901	3/5/2016	1	1	0	0
Site	1	Italy	3902	7/1/2016	1	1	0	0
Site	1	Italy	3903	8/21/2018	0	0	0	0
Study	2			10/15/2018				1
Region	2	Netherlands		5/1/2017	0	0	1	0
Site	2	Netherlands	3101	4/1/2019	0	0	0	0
Region	2	Spain		10/2/2018	0	0	1	0
Site	2	Spain	3401	9/1/2017	1	1	0	0
Site	2	Spain	3402	5/1/2016	1	1	0	0
Region	2	Sweden		12/22/2021	0	0	0	0
Site	2	Sweden	4601	5/2/2019	1	1	0	0
Region	2	United Kingdom		3/2/2019	0	0	0	0

[Anonymous]

Hi @kza40381 ,

Try codes as below to create calcualted columns.

Site FSI < Region FSI = 
VAR _REGIN_DATE =
    CALCULATE (
        MIN ( 'Table'[FSI] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Study #], 'Table'[Country] ),
            'Table'[Heirarchy level] = "Region"
        )
    )
RETURN
    IF (
        'Table'[Heirarchy level] = "Study",
        BLANK (),
        IF ( 'Table'[FSI] < _REGIN_DATE && 'Table'[FSI] <> BLANK (), 1, 0 )
    )

Site FSI < Study FSI = 
VAR _STUDY_DATE =
    CALCULATE (
        MIN ( 'Table'[FSI] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Study #] ),
            'Table'[Heirarchy level] = "Study"
        )
    )
RETURN
    IF (
        'Table'[Heirarchy level] = "Study",
        BLANK (),
        IF (
            'Table'[FSI] < _STUDY_DATE
                && 'Table'[FSI] <> BLANK ()
                && 'Table'[Heirarchy level] = "Site",
            1,
            0
        )
    )

Min Region < Study = 
VAR _STUDY_DATE =
    CALCULATE (
        MIN ( 'Table'[FSI] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Study #] ),
            'Table'[Heirarchy level] = "Study"
        )
    )
RETURN
    IF (
        'Table'[Heirarchy level] = "Study",
        BLANK (),
        IF ( 'Table'[Heirarchy level] = "Region" && 'Table'[FSI] < _STUDY_DATE, 1, 0 )
    )

Study has either Region or Site FSI  which is < Study FSI = 
VAR _COUNT_SITE =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Study #] ),
            'Table'[Site FSI < Study FSI] = 1
        )
    )
VAR _COUNT_REGION =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Study #] ),
            'Table'[Min Region < Study] = 1
        )
    )
RETURN
    IF (
        'Table'[Heirarchy level] = "Study"
            && _COUNT_REGION + _COUNT_SITE > 0,
        1,
        0
    )

Result is as below.

I think [Site FSI < Region FSI] in Study# =1 Country = Israel and [Site FSI < Study FSI] in Study# =2 Country = Sweden are not the same like yours.

Best Regards,
Rico Zhou

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

[kza40381]

Hi Rico,

I can't tell you how excited I am.  I just replaced all of the generic names in your code noted in my sample in all of the columns you prepared.  They all worked!  I am so thrilled as I have been working on this for weeks and new it was about filtering and all accept.  Just couldn't get it right as I am very novice to power BI and Dax.  Please let me know how I can give you the highest rating on this complicated task/solution.  I also appreciate you caught the mistake in my sample.  Thank you for that!

[Anonymous · Accepted solution]

Hi @kza40381 ,

I am so glad to help you solve your problem. Please kindly Accept it as the solution. More people will benefit from it. 

Best Regards,
Rico Zhou

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

[lbendlin]

Yes, this helps.  However the expected result doesn't seem to match your logic. For example for Israel the first two site FSIs are lower than the region FSI yet they are not marked?

See attached for a proposal on the first extra column.

[kza40381]

Thank you so much for responding Ibendlin.  As noted above, Rico was able to solve my issues.  I can't tell you how appreciative I am that all of you in this community are willing to help those of us who struggle trying to figure things out.  You are all brilliant!