Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

DAX Measure Help

Hello I've got this setup in Power BI: I'm trying to create a measure, which can provide the "startdate" of a specific "type" (Tabel 1) for a visual with a list of "SocialSecurityNo" from Tab...
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    2 years ago

    i think your data model is not good.

     

    table 2 and 3 are linked by student ID. That is a many to many relationship.

    ID 300 may have two social security no and each one will have two agreeement?

     

     

    I used a,b,c,d,e for social security No.

    measure =
    VAR _id=maxx(FILTER(Table2,Table2[StudentID]=max(Table3[StudentID])),Table2[AgreementID])
    VAR _type=maxx(FILTER(Table1,Table1[AgreementID]=_id),Table1[Type])
    return if(_type="VFO",maxx(FILTER(Table1,Table1[AgreementID]=_id),Table1[Start]),blank())
     
     
    pls see the attachment below.
     

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

I have a visual with SocialScurityNo.
I need to add for exaple the startdate for type "VFO" if the student has any "type VFO" - else it need to be blank.
Do this answer your question?

 

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    2 years ago

    i think your data model is not good.

     

    table 2 and 3 are linked by student ID. That is a many to many relationship.

    ID 300 may have two social security no and each one will have two agreeement?

     

     

    I used a,b,c,d,e for social security No.

    measure =
    VAR _id=maxx(FILTER(Table2,Table2[StudentID]=max(Table3[StudentID])),Table2[AgreementID])
    VAR _type=maxx(FILTER(Table1,Table1[AgreementID]=_id),Table1[Type])
    return if(_type="VFO",maxx(FILTER(Table1,Table1[AgreementID]=_id),Table1[Start]),blank())
     
     
    pls see the attachment below.
     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Sorry. In tabel 3 there is only one Student ID per SpcialSecurityNo, so it will be fine, I guess. I will try to translate your measure into my datamodel 🙂