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 ...
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.

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.

    • kza40381's avatar
      kza40381
      Frequent Visitor
      4 years ago

      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's avatar
        Anonymous
        Not applicable
        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.

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

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

      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!