Forum Discussion

kza40381's avatar
kza40381
Frequent Visitor
4 years ago
Solved

Find min date within a group in a heirarchy

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.

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

7 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      kza40381
      Frequent Visitor
      4 years ago

      Thank you for your response.  Is this helpful?

      Heirarchy levelStudy #CountrySiteFSISite FSI < Region FSISite FSI < Study FSIMin Region < StudyStudy has either Region or Site FSI  which is < Study FSI
      Study1  8/15/2018   1
      Region1Israel 3/29/20190 00
      Site1Israel972110/31/20180000
      Site1Israel972211/13/20180 00
      Site1Israel9723(Blank)0000
      Region1Italy 1/1/20170010
      Site1Italy39013/5/20161100
      Site1Italy39027/1/20161100
      Site1Italy39038/21/20180000
      Study2  10/15/2018   1
      Region2Netherlands 5/1/20170010
      Site2Netherlands31014/1/20190000
      Region2Spain 10/2/20180010
      Site2Spain34019/1/20171100
      Site2Spain34025/1/20161100
      Region2Sweden 12/22/20210000
      Site2Sweden46015/2/20191100
      Region2United Kingdom 3/2/20190000
      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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.