Forum Discussion
Issus combining USERELATIONSHIP and FILTER in a measure
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.
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:
- Removed the "('Issues'[Resolved] <> BLANK()) line since it wasn't adding any value to my results.
- Added "ALL" to my FILTER line
- 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])ReturnIF ( 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!!!
Super UserAnonymous , Try like
Shipped Monthly =
CALCULATE (
COUNT ('Issues'[Resolved]), ('Issues'[State] = "Shipped" ) ,
USERELATIONSHIP ( 'Issues'[Resolved], 'Date'[Date] ) ,
FILTER (ALL ('DATE'), ( 'DATE'[DATE ] <= MAX ('DATE'[DATE] ) ) ))
or
Shipped Monthly =
CALCULATE (
COUNT ('Issues'[Resolved]), ('Issues'[State] = "Shipped" ) ,
USERELATIONSHIP ( 'Issues'[Resolved], 'Date'[Date] ) ,
FILTER (ALLSELECTED ('DATE'), ( 'DATE'[DATE ] <= MAX ('DATE'[DATE] ) ) ))
Sorry, I forgot to mention that I indeed tried this Filter suggested but with no success.
In my attempt to correct the filter issue, I realized the following 2 scenarios:
1. Shipped Monthly =
CALCULATE (
COUNT (‘Issues'[Resolved]), (‘Issues'[State] = "Shipped" ) ,
USERELATIONSHIP ( ‘Issues'[Resolved], 'Date'[Date] ) ,
FILTER (ALL (‘Issues'), ( ‘Issues'[Resolved ] <= MAX (‘Issues'[Resolved] ) ) )
Month | Upload Total | Shipped Total |
January | 1 |
|
February | 4 |
|
March | 8 |
|
April | 9 |
|
May | 16 |
|
June | 24 |
|
July |
|
|
August | 32 |
|
September | 48 |
|
October | 56 |
|
November | 57 |
|
December |
|
|
2. And when I use the following filter, I get the total Shipped in every total field in the table (see below)
Shipped Monthly =
CALCULATE (
COUNT (‘Issues'[Resolved]), (‘Issues'[State] = "Shipped" ) ,
USERELATIONSHIP ( ‘Issues'[Resolved], 'Date'[Date] ) ,
FILTER (ALL (‘Date), ( ‘Date [Date] <= MAX (‘Date [Date] ) ) )
Month | Upload Total | Shipped Total |
January | 1 | 33 |
February | 4 | 33 |
March | 8 | 33 |
April | 9 | 33 |
May | 16 | 33 |
June | 24 | 33 |
July |
| 33 |
August | 32 | 33 |
September | 48 | 33 |
October | 56 | 33 |
November | 57 | 33 |
December |
| 33 |
I have tried several syntax combinations but with no success. I know I must be overlooking something very simple because everything I have read online tells me this measure should work. Do you have any other ideas?
The 1st scenario returns a blanks in every Shipped Total cell and the 2nd scenario returns the total number of Shipped (33) in every cell of the Shipped Total. Any help is appreciated.