Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Relationship not working as expected

Hi all, 

 

I have a below formula where I am calculating a measure. One of the columns I am referring to is IDENTITY_IDENTIFIER, which is basically my customer ID. When I create a new relationship with a new table based on this column, the value I am getting from this measure is not working as expected, eventhough I do not change anything in my graph. I could not figure out why this would be happening, hoping for your guidance

 

Two_device_users_count =
VAR tmpEff = ADDCOLUMNS('Device link data',"Effective Date",IF(ISBLANK([THIRD_LINK_DATE]),TODAY(),[THIRD_LINK_DATE]))
VAR tmpTable =  
SELECTCOLUMNS(
    FILTER(
        GENERATE(
            tmpEff,
            'Date_Lookup'
        ),
        [Date] >= [SECOND_LINK_DATE] &&
        [Date] < [Effective Date] &&
        [SECOND_LINK_DATE]<> BLANK()
    ),
    "ID",[IDENTITY_IDENTIFIER],
    "Date",[Date]
)
VAR tmpTable1 = GROUPBY(tmpTable,[ID],"Count",COUNTX(CURRENTGROUP(),[Date]))
RETURN  IF(ISBLANK(COUNTROWS(tmpTable1)),0, COUNTROWS(tmpTable1) )
Need Help

3 Replies

  • rubayatyasmin's avatar
    rubayatyasmin
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Anonymous 

     

    Check your relationship type. one-to-one, one-to-many, etc. Check for duplicate values. Also check the direction. I mean bi-direction or single. if bi-direction then it will impact on your measure calculation. Make it single. 

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi rubayatyasmin thank you for your response. The relationship is one to one and the direction is 'both'.

     

    When I try to change the cross filter direction to single, I get the error: 'The filter direction you selected isn't valid for this relationship'

    • rubayatyasmin's avatar
      rubayatyasmin
      Icon for Community Champion rankCommunity Champion
      3 years ago

      I presume this is causing the issue. Try transforming the tables in the PQ editor and try making a fact and dimension table. And try to make the relationship one-to-many. Or if possible try to merge the tables and keep the needed column for calculation. 

       

      Did I help? If yes, hit 👍 and accept this answer as solution. 

       

      Thanks