Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Issus combining USERELATIONSHIP and FILTER in a measure

Hi, I am very new to Power BI and I can’t seem to resolve my most recent issue.  I have searched for a solution to this issue without any success.  If someone out there can help me, I would really a...
Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    5 years ago

    Hi, Anonymous 

     

    You can try this:

    If it doesn't work correctly,maybe it’s a behavior of ‘USERELATIONSHIP’,The filter added later still uses the original relationship.If you don't use filter, the 'calculate' function will automatically filter the corresponding data in inactive relationships(date-resolved) based on the current context.

    If it doesn’t solve your problem, please feel free to ask me.

     

    Best Regards

    Janey Guo

     

    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
    5 years ago

    After additional research and debugging, I was able to come up with the following measure that does exactly what I need.  Below is what I changed:

    1. Removed the "('Issues'[Resolved] <> BLANK()) line since it wasn't adding any value to my results.  
    2. Added "ALL" to my FILTER line
    3. Added VARs to enable the readability,  enable the year and month compare in my IF statement to display values in my line graph up to the current year/month.

     

    VAR FirstShippedDate = CALCULATE(MINX('Issues', 'Issues'[Resolved] ) , ('Issues'[State] = "Shipped" ), ALL ('Issues' ))
    VAR LastShippedDate = CALCULATE (MAXX('Issues', 'Issues'[Resolved - Copy-yyyy-mm]) ,('Issues'[State] = "Shipped" ), ALL ('Issues' ) )
    Var MaxCalendarDate = MAXX('Date', 'Date'[Year-Month])

    Return
    IF ( MaxCalendarDate > LastShippedDate && MaxCalendarDate > TODAY(),
    BLANK(),
    CALCULATE( COUNT ( 'Issues'[Resolved] ),
    ('Issues'[State] = "Shipped" ),
    USERELATIONSHIP ('Date'[Date], 'Issues'[Resolved] ) ,
    FILTER ( ALL('Date' ), ('Date'[Date] <= MAX('Date'[Date]) ))
    ) )

     

    Thanks so much for implying that there were issues with my measure and guiding me to dig deeper.  I initially thought that a FILTER statement could not follow a USERELATIONSHIP statement, but that is not the case.  

     

    Thanks!!!

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Issue IdProjectResolvedStateUpload DateCase Options
CPP11-1Project A4 Mar 2020Canceled31 Jan 2020Plan + 3DP
CPP11-2Project A10 Mar 2020Shipped10 Feb 2020Plan + 3DP
CPP11-4Project A10 Mar 2020Shipped27 Feb 2020Plan + 3DP
CPP11-3Project A26 May 2020Shipped24 Feb 2020Plan + 3DP
CPP11-7Project A26 May 2020Shipped11 Mar 2020Plan + 3DP
CPP11-8Project A26 May 2020Shipped13 Mar 2020Plan + 3DP
CPP11-6Project A26 May 2020Shipped4 Mar 2020Plan + 3DP
CPP11-17Project A27 May 2020Canceled13 May 2020Plan + 3DP
CPP11-19Project A29 May 2020Shipped13 May 2020Plan + 3DP
CPP11-26Project A5 Jun 2020Shipped12 May 2020Plan + 3DP
CPP11-9Project A10 Jun 2020Canceled13 Mar 2020Plan + 3DP
CPP11-16Project A10 Jun 2020Canceled13 May 2020Plan + 3DP
CPP11-12Project A10 Jun 2020Canceled27 Apr 2020Plan + 3DP
CPP11-23Project A10 Jun 2020Shipped22 May 2020Plan + 3DP
CPP11-22Project A10 Jun 2020Shipped15 May 2020Plan + 3DP
CPP11-28Project A15 Jun 2020Shipped21 May 2020Plan + 3DP
CPP11-29Project A17 Jun 2020Shipped3 Jun 2020Plan + 3DP
CPP11-36Project A19 Jun 2020Canceled10 Jun 2020Plan + 3DP
CPP11-33Project A19 Jun 2020Canceled6 Jun 2020Plan + 3DP
CPP11-30Project A30 Jun 2020Shipped3 Jun 2020Plan + 3DP
CPP11-32Project A9 Jul 2020Shipped6 Jun 2020Plan + 3DP
CPP11-35Project A10 Jul 2020Canceled10 Jun 2020Plan + 3DP
CPP11-57Project A23 Jul 2020Shipped17 Jun 2020Plan + 3DP
CPP11-55Project A23 Jul 2020Shipped17 Jun 2020Plan + 3DP
CPP11-111Project A2 Sep 2020Shipped11 Aug 2020Plan + 3DP
CPP11-140Project A3 Sep 2020Canceled26 Aug 2020Plan + 3DP
CPP11-135Project A3 Sep 2020Canceled18 Aug 2020Plan + 3DP
CPP11-190Project A11 Sep 2020Canceled10 Sep 2020Plan + 3DP
CPP11-144Project A17 Sep 2020Shipped26 Aug 2020Plan + 3DP
CPP11-128Project A17 Sep 2020Shipped14 Aug 2020Plan + 3DP
CPP11-136Project A17 Sep 2020Shipped19 Aug 2020Plan + 3DP
CPP11-243Project A21 Sep 2020Canceled21 Sep 2020Plan + 3DP
CPP11-253Project A22 Sep 2020Canceled21 Sep 2020Plan + 3DP
CPP11-252Project A22 Sep 2020Canceled21 Sep 2020Plan + 3DP
CPP11-142Project A24 Sep 2020Shipped26 Aug 2020Plan + 3DP
CPP11-162Project A24 Sep 2020Shipped1 Sep 2020Plan + 3DP
CPP11-143Project A24 Sep 2020Shipped26 Aug 2020Plan + 3DP
CPP11-321Project A Manufacturing Ordered15 Nov 2020Plan + 3DP
CPP11-334Project A Manufacturing Ordered16 Nov 2020Plan + 3DP
CPP11-250Project A Review - Mechanical21 Sep 2020Plan + 3DP
CPP11-337Project A Review - Segmentation16 Nov 2020Plan + 3DP
CPP11-338Project A Review - Segmentation16 Nov 2020Plan + 3DP
CPP11-323Project A Submitted15 Nov 2020Plan + 3DP
CPP11-328Project A Submitted16 Nov 2020Review Case
CPP11-329Project A Submitted16 Nov 2020Plan Only
CPP11-332Project A Submitted16 Nov 2020Plan Only
CPP11-333Project A Submitted16 Nov 2020Plan + 3DP
CPP11-335Project A Submitted16 Nov 2020Plan + 3DP
CPP11-339Project A Submitted No case options
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    5 years ago

    Hi,

    I have jumped in late into this post but would still like to help.  Based on the sample data that you have shared, coould you clearly show the expected result.  I am not concerned with your formulas.  Just describe the business question and show me your expected result.