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

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!!!

rusgesig's avatar
rusgesig
Icon for Helper IV rankHelper IV
1 year ago

Useful post, my issue was that measure would not filter if <>blank() was before USERELATIONSHIP, removing the '[col] <> blank()' line fixed the filtering.