Forum Discussion

Saloni_Gupta's avatar
Saloni_Gupta
Helper I
2 years ago
Solved

DAX relationship filter not working

Hi All, I have data model like this:   I want to create a calculated column "JP1 or JP2" which would return the values "JP1" or "JP2" based on Job Info table. Lets say, I select Partner Name...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi  Saloni_Gupta ,

     

    Here are the steps you can follow：

    1. Create measure.

    Flag =
var _select=SELECTEDVALUE('Partner Info'[Parner Name])
var _selectID=MAXX(FILTER(ALL('Partner Info'),'Partner Info'[Parner Name]=_select),[PartnerId])
return
IF(
    MAX('PartnerJob Relationship'[Partner Id])=_selectID&&MAX('PartnerJob Relationship'[Relationship])="Managed",1,0)
    JP1 or JP2 =
var _column1=SELECTCOLUMNS('Job Info',"1ID",'Job Info'[JobPartner1Id])
var _column2=SELECTCOLUMNS('Job Info',"2ID",'Job Info'[JobPartner2Id])
return
IF(
    MAX('PartnerJob Relationship'[Partner Id]) in _column1 ,"JP1",
    IF(
        MAX('PartnerJob Relationship'[Partner Id]) in _column2,"JP2",BLANK()))

    2. Place [Flag]in Filters, set is=1, apply filter.

    3. Result:

     

     

    Best Regards,

    Liu Yang

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

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi  Saloni_Gupta ,

 

Here are the steps you can follow：

1. Create measure.

Flag =
var _select=SELECTEDVALUE('Partner Info'[Parner Name])
var _selectID=MAXX(FILTER(ALL('Partner Info'),'Partner Info'[Parner Name]=_select),[PartnerId])
return
IF(
    MAX('PartnerJob Relationship'[Partner Id])=_selectID&&MAX('PartnerJob Relationship'[Relationship])="Managed",1,0)
JP1 or JP2 =
var _column1=SELECTCOLUMNS('Job Info',"1ID",'Job Info'[JobPartner1Id])
var _column2=SELECTCOLUMNS('Job Info',"2ID",'Job Info'[JobPartner2Id])
return
IF(
    MAX('PartnerJob Relationship'[Partner Id]) in _column1 ,"JP1",
    IF(
        MAX('PartnerJob Relationship'[Partner Id]) in _column2,"JP2",BLANK()))

2. Place [Flag]in Filters, set is=1, apply filter.

3. Result:

 

 

Best Regards,

Liu Yang

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

  • Saloni_Gupta's avatar
    Saloni_Gupta
    Helper I
    2 years ago

    Hi Anonymous 
    Thanks for your reply, 
    I have one question here - what if I dont want to show the column "Relationship" in the final table, I dont think the Flag calculation would display any result. Is there any way to modify the dax to still work when Relationship column is not shown?