Issus combining USERELATIONSHIP and FILTER in a measure

[Anonymous]

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 appreciate it.

I have created the measure below which creates the table below:

Shipped Monthly =

CALCULATE (  

    COUNT (‘Issues'[Resolved]),  (‘Issues'[State] = "Shipped" ) ,

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

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	57	4
December

Data Details:

1. The month column is the month from the Date table.
2. The Upload Total is derived via the active relationship between the Issues[Resolved Date] and the Date[date]
3. The Shipped Total is derived via the inactive relationship between the Issues[Resolved Date] and the Date[date]
4. All my data is correct

My Issues:

1. I am not able to get a running total for the Shipped Total column even when I add a filter to my measure.  I updated my measure to look like the syntax below and instead of getting a running total the entire Shipped Total column is blanked out.  This made me think it was datatype related but I doubled checked to be sure all my dates were indeed similar date data types.  I also noticed that if I comment out my USERELATIONSHIP line the filter line is executed as expected but then uses the active relationship which returns incorrect data.  I need the filter to be executed after the USERELATIONSHIP to return the correct data.  Can someone please help me correct this measure?

Shipped Monthly =

CALCULATE (  

    COUNT (‘Issues'[Resolved]),  (‘Issues'[State] = "Shipped" ) ,

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

                     FILTER (ALL (‘Issues'), ( ‘Issues'[Resolved ]  <= MAX (‘Issues'[Resolved] ) ) )   

1. I would like to replace all blank values with the previous months total value, but I don’t know how to do this.  For example, I would like the Upload Total for the month of July to be 24.  Again the Upload Total is leveraging an active relationship.  Syntax for the inactive relationship would be helpful as well if it is different from the active relationship.   This will correct the gap I am seeing in my line graph. 

Active Relationship

Inactive Relationship

[v-janeyg-msft · Accepted solution]

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]

sorry for all the typing mistakes.  please overlook them.

[Anonymous]

Thank you so much for your response.  FYI:  The measure works great without the FILTER statement.  The issue I am incountering is that the FILTER statement I have added to the end of the measure is not providing me a running total in the "Shipped Total" column as expected.  Dy you have any advise about how to incoorporate this FILTER statement so that I can get the running total?  Below is the measure I am using with the filter included.  

Shipped Monthly = (

CALCULATE (COUNT ('Issues'[Resolved]),

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

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

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

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

Running this measure creates the following data in the "Shipped Total' column".  It appears to be ingnoring the FILTER completly.  

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

The desired results should look as follows in the "Shipped Total' column":

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

Does anyone have any advice on how I can get the running total to appear in the 'Shipping Total" column?  

Any advice provided would be most appreciated.  I am completly out of ideas!!!

[v-janeyg-msft · Accepted solution]

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 · Accepted solution]

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]

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

[v-janeyg-msft]

Hi, @Anonymous 

Try this:

Shipped Total =
(
    CALCULATE (
        COUNT ( 'Issues'[Resolved] ),
         ( 'Issues'[State] = "Shipped" ),
         ( 'Issues'[Resolved] <> BLANK () ),
        USERELATIONSHIP ( 'Date'[Date], 'Issues'[Resolved] )
    )
)

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]

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:

1. 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] )))

)))

))

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

[Anonymous]

Issue Id	Project	Resolved	State	Upload Date	Case Options
CPP11-1	Project A	4 Mar 2020	Canceled	31 Jan 2020	Plan + 3DP
CPP11-2	Project A	10 Mar 2020	Shipped	10 Feb 2020	Plan + 3DP
CPP11-4	Project A	10 Mar 2020	Shipped	27 Feb 2020	Plan + 3DP
CPP11-3	Project A	26 May 2020	Shipped	24 Feb 2020	Plan + 3DP
CPP11-7	Project A	26 May 2020	Shipped	11 Mar 2020	Plan + 3DP
CPP11-8	Project A	26 May 2020	Shipped	13 Mar 2020	Plan + 3DP
CPP11-6	Project A	26 May 2020	Shipped	4 Mar 2020	Plan + 3DP
CPP11-17	Project A	27 May 2020	Canceled	13 May 2020	Plan + 3DP
CPP11-19	Project A	29 May 2020	Shipped	13 May 2020	Plan + 3DP
CPP11-26	Project A	5 Jun 2020	Shipped	12 May 2020	Plan + 3DP
CPP11-9	Project A	10 Jun 2020	Canceled	13 Mar 2020	Plan + 3DP
CPP11-16	Project A	10 Jun 2020	Canceled	13 May 2020	Plan + 3DP
CPP11-12	Project A	10 Jun 2020	Canceled	27 Apr 2020	Plan + 3DP
CPP11-23	Project A	10 Jun 2020	Shipped	22 May 2020	Plan + 3DP
CPP11-22	Project A	10 Jun 2020	Shipped	15 May 2020	Plan + 3DP
CPP11-28	Project A	15 Jun 2020	Shipped	21 May 2020	Plan + 3DP
CPP11-29	Project A	17 Jun 2020	Shipped	3 Jun 2020	Plan + 3DP
CPP11-36	Project A	19 Jun 2020	Canceled	10 Jun 2020	Plan + 3DP
CPP11-33	Project A	19 Jun 2020	Canceled	6 Jun 2020	Plan + 3DP
CPP11-30	Project A	30 Jun 2020	Shipped	3 Jun 2020	Plan + 3DP
CPP11-32	Project A	9 Jul 2020	Shipped	6 Jun 2020	Plan + 3DP
CPP11-35	Project A	10 Jul 2020	Canceled	10 Jun 2020	Plan + 3DP
CPP11-57	Project A	23 Jul 2020	Shipped	17 Jun 2020	Plan + 3DP
CPP11-55	Project A	23 Jul 2020	Shipped	17 Jun 2020	Plan + 3DP
CPP11-111	Project A	2 Sep 2020	Shipped	11 Aug 2020	Plan + 3DP
CPP11-140	Project A	3 Sep 2020	Canceled	26 Aug 2020	Plan + 3DP
CPP11-135	Project A	3 Sep 2020	Canceled	18 Aug 2020	Plan + 3DP
CPP11-190	Project A	11 Sep 2020	Canceled	10 Sep 2020	Plan + 3DP
CPP11-144	Project A	17 Sep 2020	Shipped	26 Aug 2020	Plan + 3DP
CPP11-128	Project A	17 Sep 2020	Shipped	14 Aug 2020	Plan + 3DP
CPP11-136	Project A	17 Sep 2020	Shipped	19 Aug 2020	Plan + 3DP
CPP11-243	Project A	21 Sep 2020	Canceled	21 Sep 2020	Plan + 3DP
CPP11-253	Project A	22 Sep 2020	Canceled	21 Sep 2020	Plan + 3DP
CPP11-252	Project A	22 Sep 2020	Canceled	21 Sep 2020	Plan + 3DP
CPP11-142	Project A	24 Sep 2020	Shipped	26 Aug 2020	Plan + 3DP
CPP11-162	Project A	24 Sep 2020	Shipped	1 Sep 2020	Plan + 3DP
CPP11-143	Project A	24 Sep 2020	Shipped	26 Aug 2020	Plan + 3DP
CPP11-321	Project A		Manufacturing Ordered	15 Nov 2020	Plan + 3DP
CPP11-334	Project A		Manufacturing Ordered	16 Nov 2020	Plan + 3DP
CPP11-250	Project A		Review - Mechanical	21 Sep 2020	Plan + 3DP
CPP11-337	Project A		Review - Segmentation	16 Nov 2020	Plan + 3DP
CPP11-338	Project A		Review - Segmentation	16 Nov 2020	Plan + 3DP
CPP11-323	Project A		Submitted	15 Nov 2020	Plan + 3DP
CPP11-328	Project A		Submitted	16 Nov 2020	Review Case
CPP11-329	Project A		Submitted	16 Nov 2020	Plan Only
CPP11-332	Project A		Submitted	16 Nov 2020	Plan Only
CPP11-333	Project A		Submitted	16 Nov 2020	Plan + 3DP
CPP11-335	Project A		Submitted	16 Nov 2020	Plan + 3DP
CPP11-339	Project A		Submitted		No case options

[Ashish_Mathur]

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.

[v-janeyg-msft]

Hi, @Anonymous 

It’s my pleasure to answer for you.

Could you mind providing some sample data and the desired result?So we can help you soon.

Best Regards

Janey Guo

[amitchandak]

@Anonymous , 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] ) ) ))

[Anonymous]

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?

[Anonymous]

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.