Forum Discussion

ryan-gao's avatar
ryan-gao
Helper III
9 years ago
Solved

userelationship puzzel

 

I want to use the expand table of "control" and "date" to filter "bg", at the same time ,blocking the relationship between date  and product ,how to write the formula?

thanks!

Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    Hi ryan-gao,

    For the count_p_rel measure, you use USERELATIONSHIP(bg[date_b],'date'[date]), which is only used relationship between bg[date_b] and 'date'[date]. While you calculate COUNTROWS('product'), which is not effected by USERELATIONSHIP(bg[date_b],'date'[date]). Only when you calculate COUNTROWS(bg) or COUNTROWS('date'), USERELATIONSHIP(bg[date_b],'date'[date]) can take effect.

    Best Regards,
    Angelia

4 Replies

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    9 years ago

    Hi ryan-gao,

    Your requirement is still confusing, what your resource table looks like, and what result would you want to achieve? So that I can post solution which is close to your desired result.

    Best Regards,
    Angelia

    • ryan-gao's avatar
      ryan-gao
      Helper III
      9 years ago

      thanks you  v-huizhn-msft, I figured it out,

      about userelationship function,I had another question:

      it seems not always blocking the active relationship, for example  the count_p_rel measure

      • v-huizhn-msft's avatar
        v-huizhn-msft
        Microsoft Employee
        9 years ago

        Hi ryan-gao,

        For the count_p_rel measure, you use USERELATIONSHIP(bg[date_b],'date'[date]), which is only used relationship between bg[date_b] and 'date'[date]. While you calculate COUNTROWS('product'), which is not effected by USERELATIONSHIP(bg[date_b],'date'[date]). Only when you calculate COUNTROWS(bg) or COUNTROWS('date'), USERELATIONSHIP(bg[date_b],'date'[date]) can take effect.

        Best Regards,
        Angelia