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
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
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:
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])

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

Not applicable

5 years ago

I have uploaded a sample data file. I have also modified my Shipped Total measure and I am no longer getting the total shipped on every line in the table as before. Progress however small is always good.

Details:

My Upload Total Measure works perfectly. The Date Table(1) and the Issues Table (many) have an active relationship setup (measure below):

Upload Total = (CALCULATE (

IF (Calculate (COUNT ('Issues'[Issue Id]) = BLANK() ) ,

(Calculate (COUNT ('Issues'[Issue Id] ) ,

FILTER(ALLSELECTED('Issues'),

('Issues'[Upload Date] <= MAX('Issues'[Upload Date]))),

DATEADD ('Issues'[Upload Date], -2, MONTH )

)) ,

(Calculate (COUNT ('Issues'[Issue Id] ),

FILTER(ALLSELECTED('Issues'),

('Issues'[Upload Date] <= MAX ('Issues'[Upload Date] )))

)))

))

My Shipped Total Measure is not as successful. It does calculate the monthly shipping count as expected and it utilizes the inactive relationship setup (Date Table (1) to Issue Table (many) ) correctly. I just can't seem to figure out how to get a running total for the measure. The measure I am currently using is below and I get the same results in my table whether I include the FILTER line or not.

Shipped Total = (CALCULATE (

COUNT ('Issues'[Resolved]),

('Issues'[State] = "Shipped" ) ,

('Issues'[Resolved] <> BLANK() ) ,

USERELATIONSHIP ('Date'[Date], 'Issues'[Resolved] ) ,

FILTER (('Date'), 'Date'[Date] <= MAX ('Date'[Date]) )

My table results are below.

Month	Upload Total	Shipped Total
January	1
February	4
March	8	2
April	9
May	16	5
June	24	6
July		3
August	32
September	48	7
October	56	6
November	70	6
December

I also noticed that if I switch the FILTER statement to

"FILTER(ALLSELECTED('Issues'),'Issues'[Resolved] <= MAX ('Issues'[Resolved] ) ) "

I get no results in the Shipped Total column of my table. I even tried replacing the "ALLSELECTED" with "ALL" or removing it entirely and I still ended up with a blank Shipped Total column. So it seems that using the following filter is more correct but like I said before it will not create the running total for me.

FILTER (('Date'), 'Date'[Date] <= MAX ('Date'[Date]) )

Any advice you can provide would be most appreciated!!!

Forum Discussion

Issus combining USERELATIONSHIP and FILTER in a measure