USERELATIONSHIP is not working.

[KL001]

Hello,

I am looking to calculate the percentage of on-time audits and plot them by their due date. The default relationship between the date table and the audit table uses the audit end date. When I filter to Jan 2019 it looks as though the default relationship is being maintained. Why won't it plot it by the calculated due date?

[KL001 · Accepted solution]

I found the solution. I need to move the variable definition within my final calculate statement. Otherwise, the variables are calculated outside of the filter context created by the USERELATIONSHIP statement. 

RADACAD has a good article explaining it further: https://radacad.com/caution-when-using-variables-in-dax-and-power-bi

[Ashish_Mathur]

Hi,

Share some data (in a format the can be pasted in an MS Excel file) and show the expected result.

[KL001]

Here is the expected result, obtained when only one relationship between Date[Date] and 'Audit Master'[

Calculated Report Due Date] exists (The bar graph is plotting by the calculated due date) the second photo shows what is obtained when I rely on USERELATIONSHIP:

Here are the two calculated columns and Measure used:

'Audit Master'[Audit Report Days Overdue] =

Var EndDate = 'Audit Master'[initial_report_dstrbtd_on]

Var DueDate = 'Audit Master'[audit_end_date] + 30

Return

SWITCH(TRUE(),

DATEDIFF(DueDate,EndDate,Day) <= 0, "X < 0",

DATEDIFF(DueDate,EndDate,Day) > 0 && DATEDIFF(DueDate,EndDate,Day) < 15, "0 < X < 15",

DATEDIFF(DueDate,EndDate,Day) > 15 && DATEDIFF(DueDate,EndDate,Day) < 30, "15 < X < 30",

DATEDIFF(DueDate,EndDate,Day) > 30 && DATEDIFF(DueDate,EndDate,Day) < 45, "30 < X < 45",

DATEDIFF(DueDate,EndDate,Day) > 45 && DATEDIFF(DueDate,EndDate,Day) < 60, "45 < X < 60",

DATEDIFF(DueDate,EndDate,Day) > 60 , "X > 60")

'Audit Master'[Calculated Report Due Date] = 'Audit Master'[audit_end_date] + 30

[% on Time: Audit Report] =

Var TotalAudits = CALCULATE(COUNTROWS('Audit Master'),'Audit Master'[audit_start_date] <> blank() || 'Audit Master'[current_state] <> "Closed - Cancelled")

Var OnTimeAudits = CALCULATE(COUNTROWS('Audit Master'),'Audit Master'[Audit Report Days Overdue] = "X < 0")

Return

CALCULATE(OnTimeAudits/TotalAudits,USERELATIONSHIP('Date'[Date],'Audit Master'[Calculated Report Due Date]))

[KL001]

id	current_state	initial_report_dstrbtd_on	audit_end_date	audit_start_date
1	Closed - Complete	2/12/2019	2/12/2019	2/12/2019
2	Closed - Complete	2/11/2019	2/11/2019	2/11/2019
3	Closed - Complete	2/20/2019	2/19/2019	2/19/2019
4	Closed - Complete	2/15/2019	2/15/2019	2/15/2019
5	Closed - Complete	2/6/2019	2/6/2019	2/6/2019
6	Closed - Complete	2/15/2019	2/15/2019	2/15/2019
7	Closed - Complete	2/11/2019	2/11/2019	2/11/2019
8	Closed - Complete	3/8/2019	3/7/2019	3/6/2019
9	Closed - Complete	2/19/2019	2/19/2019	2/18/2019
10	Closed - Complete	3/7/2019	3/7/2019	3/7/2019
11	Closed - Complete	1/15/2019	1/15/2019	1/15/2019
12	Closed - Complete	3/7/2019	3/7/2019	3/7/2019
13	Closed - Complete	2/15/2019	2/14/2019	2/14/2019
14	Closed - Complete	3/28/2019	3/28/2019	3/28/2019
15	Closed - Complete	2/18/2019	2/18/2019	2/18/2019
16	Closed - Complete	3/25/2019	3/25/2019	3/25/2019
17	Closed - Complete	3/13/2019	3/13/2019	3/13/2019
18	Closed - Complete	3/13/2019	3/13/2019	3/13/2019
19	Closed - Complete	3/8/2019	3/8/2019	3/8/2019
20	Closed - Complete	2/1/2019	2/1/2019	2/1/2019
21	Closed - Complete	3/7/2019	3/7/2019	3/7/2019
22	Closed - Complete	2/28/2019	2/28/2019	2/28/2019
23	Closed - Complete	2/15/2019	2/13/2019	2/13/2019
24	Closed - Complete	3/7/2019	3/7/2019	3/7/2019
25	Closed - Complete	3/18/2019	3/18/2019	3/18/2019
26	Closed - Complete	3/18/2019	3/18/2019	3/18/2019
27	Closed - Complete	3/1/2019	3/1/2019	3/1/2019
28	Closed - Complete	2/26/2019	2/26/2019	2/26/2019
29	Closed - Complete	3/28/2019	3/28/2019	3/28/2019
30	Closed - Complete	3/11/2019	3/11/2019	3/11/2019
31	Closed - Complete	3/29/2019	3/29/2019	3/29/2019
32	Closed - Complete	3/26/2019	3/25/2019	3/25/2019
33	Closed - Complete	3/26/2019	3/26/2019	3/26/2019
34	Closed - Complete	1/2/2019	1/2/2019	1/2/2019
35	Closed - Complete	3/13/2019	3/13/2019	3/12/2019
36	Closed - Complete	1/15/2019	1/15/2019	1/14/2019
37	Closed - Complete	3/13/2019	3/13/2019	3/13/2019
38	Closed - Complete	2/22/2019	2/21/2019	2/21/2019
39	Closed - Complete	2/22/2019	2/21/2019	2/21/2019
40	Closed - Complete	2/11/2019	2/11/2019	2/11/2019
41	Closed - Complete	3/4/2019	3/4/2019	3/4/2019
42	Closed - Complete	3/26/2019	3/25/2019	3/25/2019
43	Closed - Complete	2/22/2019	2/22/2019	2/22/2019
44	Closed - Complete	3/7/2019	3/7/2019	3/7/2019
45	Closed - Complete	3/12/2019	3/12/2019	3/12/2019
46	Closed - Complete	3/15/2019	3/15/2019	3/15/2019
47	Closed - Complete	3/5/2019	3/5/2019	3/5/2019
48	Closed - Complete	3/8/2019	3/8/2019	3/7/2019
49	Closed - Complete	2/20/2019	2/20/2019	2/20/2019
50	Closed - Complete	3/7/2019	3/7/2019	3/7/2019
51	Closed - Complete	2/28/2019	2/28/2019	2/28/2019
52	Closed - Complete	2/5/2019	2/5/2019	2/5/2019
53	Closed - Complete	1/29/2019	1/29/2019	1/29/2019
54	Closed - Complete	3/25/2019	3/25/2019	3/12/2019
55	Closed - Complete	1/30/2019	1/30/2019	1/30/2019
56	Closed - Complete	2/18/2019	2/18/2019	2/18/2019
57	Closed - Complete	3/14/2019	3/14/2019	3/6/2019
58	Closed - Complete	2/19/2019	2/19/2019	2/18/2019
59	Closed - Complete	1/30/2019	1/30/2019	1/30/2019
60	Closed - Complete	2/21/2019	2/21/2019	2/20/2019
61	Closed - Complete	2/20/2019	2/20/2019	2/19/2019
62	Closed - Complete	2/18/2019	2/18/2019	2/18/2019
63	Closed - Complete	3/20/2019	3/19/2019	3/19/2019
64	Closed - Complete	1/4/2019	1/4/2019	1/4/2019
65	Closed - Complete	3/1/2019	3/1/2019	3/1/2019
66	Closed - Complete	2/18/2019	2/15/2019	2/15/2019
67	Closed - Complete	2/6/2019	2/6/2019	2/6/2019
68	Closed - Complete	1/24/2019	1/24/2019	1/24/2019
69	Closed - Complete	3/1/2019	3/1/2019	3/1/2019
70	Closed - Complete	3/29/2019	3/27/2019	3/27/2019
71	Closed - Complete	2/13/2019	2/13/2019	2/8/2019
72	Closed - Complete	2/13/2019	2/13/2019	2/8/2019
73	Closed - Complete	2/26/2019	2/26/2019	2/26/2019
74	Closed - Complete	3/1/2019	3/1/2019	3/1/2019
75	Closed - Complete	1/18/2019	1/18/2019	1/18/2019
76	Closed - Complete	3/1/2019	3/1/2019	3/1/2019
77	Closed - Complete	3/27/2019	3/27/2019	3/25/2019
78	Closed - Complete	1/25/2019	1/25/2019	1/25/2019
79	Closed - Complete	1/25/2019	1/23/2019	1/23/2019
80	Closed - Complete	7/17/2019	2/26/2019	2/26/2019
81	Closed - Complete	2/13/2020	3/7/2019	3/6/2019
82	Pending Report		1/11/2019	1/11/2019
83	Closed - Complete	3/19/2021	3/27/2019	3/26/2019
84	Closed - Complete	1/25/2019	1/25/2019	1/25/2019
85	Closed - Complete	1/4/2019	1/4/2019	1/3/2019
86	Closed - Complete	1/24/2019	1/24/2019	1/18/2019
87	Closed - Complete	1/3/2019	1/3/2019	12/20/2018
88	Closed - Complete	3/5/2019	3/5/2019	3/5/2019
89	Closed - Complete	5/13/2019	3/4/2019	2/13/2019
90	Closed - Complete	2/1/2020	3/28/2019	3/27/2019
91	Closed - Complete	3/19/2019	3/19/2019	3/19/2019
92	Closed - Complete	1/25/2019	1/25/2019	1/9/2019
93	Closed - Complete	1/18/2019	1/18/2019	1/18/2019
94	Closed - Complete	2/15/2019	2/14/2019	2/14/2019
95	Closed - Complete	2/20/2019	2/20/2019	2/20/2019
96	Closed - Complete	2/11/2019	2/7/2019	2/7/2019
97	Closed - Complete	1/17/2019	1/14/2019	1/14/2019
98	Closed - Complete	1/8/2019	1/8/2019	1/8/2019
99	Closed - Complete	4/23/2019	2/26/2019	2/26/2019
100	Closed - Complete	12/12/2019	1/7/2019	1/7/2019
101	Closed - Complete	1/30/2019	1/30/2019	1/30/2019
102	Closed - Complete	1/2/2019	1/2/2019	1/2/2019
103	Closed - Complete	1/25/2019	1/25/2019	1/25/2019
104	Closed - Complete	4/15/2020	2/6/2019	2/6/2019
105	Closed - Complete	1/25/2019	1/25/2019	1/25/2019
106	Closed - Complete	7/2/2020	2/26/2019	2/26/2019
107	Closed - Complete	2/4/2019	2/4/2019	2/4/2019
108	Closed - Complete	1/24/2019	1/24/2019	1/24/2019
109	Closed - Complete	4/1/2019	3/29/2019	3/29/2019
110	Closed - Complete	2/1/2019	2/1/2019	2/1/2019
111	Closed - Complete	4/30/2020	3/7/2019	3/4/2019
112	Closed - Complete	3/16/2020	3/15/2019	3/15/2019
113	Closed - Cancelled		2/7/2019	1/16/2019
114	Closed - Complete	10/2/2019	1/25/2019	1/22/2019
115	Closed - Complete	7/26/2019	3/22/2019	3/20/2019
116	Closed - Complete	1/29/2019	1/29/2019	1/29/2019
117	Closed - Complete	3/19/2019	3/19/2019	3/19/2019
118	Closed - Complete	1/23/2019	1/16/2019	1/15/2019
119	Closed - Complete	3/12/2019	3/11/2019	3/11/2019
120	Closed - Complete	2/26/2019	2/26/2019	2/25/2019
121	Closed - Complete	2/26/2019	2/25/2019	2/25/2019
122	Closed - Complete	3/8/2019	3/8/2019	3/6/2019
123	Closed - Complete	3/13/2019	3/13/2019	3/11/2019
124	Closed - Complete	6/27/2019	3/13/2019	3/11/2019
125	Closed - Complete	2/25/2019	2/6/2019	2/5/2019
126	Closed - Complete	3/7/2019	2/21/2019	2/19/2019
127	Closed - Complete	5/12/2019	3/26/2019	3/25/2019
128	Closed - Complete	1/23/2019	1/10/2019	1/10/2019
129	Closed - Complete	1/31/2019	1/29/2019	1/29/2019
130	Closed - Complete	3/7/2019	3/7/2019	3/6/2019
131	Closed - Complete	1/23/2019	1/22/2019	1/21/2019
132	Closed - Complete	2/13/2019	2/12/2019	2/11/2019
133	Closed - Complete	2/20/2019	2/13/2019	2/7/2019
134	Closed - Complete	4/26/2019	3/27/2019	3/12/2019
135	Closed - Complete	5/16/2019	3/4/2019	2/8/2019
136	Closed - Complete	5/24/2019	1/31/2019	1/30/2019
137	Closed - Complete	1/16/2019	1/7/2019	1/7/2019
138	Closed - Complete	2/15/2019	2/14/2019	2/12/2019
139	Closed - Complete	2/28/2019	2/7/2019	2/6/2019
140	Closed - Complete	2/14/2019	2/5/2019	2/4/2019
141	Closed - Complete	3/26/2019	3/12/2019	3/11/2019
142	Closed - Complete	3/21/2019	3/19/2019	3/18/2019
143	Closed - Complete	2/7/2019	2/5/2019	2/4/2019
144	Closed - Complete	2/15/2019	2/15/2019	2/14/2019
145	Closed - Complete	2/19/2019	2/15/2019	2/15/2019
146	Closed - Complete	2/28/2019	2/27/2019	2/26/2019
147	Closed - Complete	2/8/2019	1/14/2019	1/9/2019
148	Closed - Complete	1/9/2019	1/8/2019	1/4/2019
149	Closed - Complete	1/11/2019	1/10/2019	1/10/2019
150	Closed - Complete	2/18/2019	2/18/2019	2/18/2019
151	Closed - Complete	1/17/2019	1/17/2019	1/17/2019
152	Closed - Complete	1/16/2019	1/15/2019	1/14/2019
153	Closed - Complete	3/14/2019	3/11/2019	3/8/2019
154	Closed - Complete	3/22/2019	3/21/2019	3/19/2019
155	Closed - Complete	1/7/2019	1/7/2019	12/19/2018
156	Closed - Complete	2/7/2019	2/5/2019	1/3/2019
157	Closed - Complete	1/8/2019	1/8/2019	12/11/2018
158	Closed - Complete	1/28/2019	1/28/2019	1/28/2019
159	Closed - Complete	3/8/2019	2/7/2019	1/16/2019
160	Closed - Complete	3/8/2019	2/7/2019	1/16/2019
161	Closed - Complete	3/13/2019	3/13/2019	3/11/2019
162	Closed - Complete	5/1/2019	3/26/2019	3/25/2019
163	Closed - Complete	3/8/2019	2/11/2019	1/21/2019
164	Closed - Complete	3/15/2019	2/27/2019	2/27/2019
165	Closed - Complete	4/26/2019	3/27/2019	3/12/2019
166	Closed - Complete	2/4/2019	2/1/2019	2/1/2019
167	Closed - Complete	3/5/2019	3/5/2019	3/5/2019
168	Closed - Complete	4/15/2019	3/15/2019	2/19/2019
169	Closed - Complete	4/25/2019	3/20/2019	3/18/2019
170	Closed - Complete	2/1/2019	1/30/2019	1/23/2019
171	Closed - Complete	2/1/2019	1/30/2019	1/23/2019
172	Closed - Complete	1/11/2019	1/9/2019	12/20/2018
173	Closed - Complete	3/21/2019	3/20/2019	3/7/2019
174	Closed - Complete	1/8/2019	1/7/2019	12/26/2018
175	Closed - Complete	1/28/2019	1/25/2019	1/18/2019

[KL001]

176	Closed - Complete	3/19/2019	3/18/2019	3/18/2019
177	Closed - Complete	3/20/2019	3/19/2019	3/5/2019
178	Closed - Complete	2/28/2019	2/28/2019	2/26/2019
179	Closed - Complete	1/11/2019	1/9/2019	12/20/2018
180	Closed - Complete	1/4/2019	1/4/2019	1/2/2019
181	Closed - Complete	2/28/2019	2/27/2019	2/21/2019
182	Closed - Complete	1/17/2019	1/17/2019	1/16/2019
183	Closed - Complete	3/18/2019	3/7/2019	2/26/2019
184	Closed - Complete	2/14/2019	1/16/2019	1/14/2019
185	Closed - Complete	4/19/2019	3/1/2019	2/27/2019
186	Closed - Complete	2/5/2019	2/4/2019	2/1/2019
187	Closed - Complete	2/7/2019	2/6/2019	2/5/2019
188	Closed - Complete	2/5/2019	2/4/2019	2/1/2019
189	Closed - Complete	2/21/2019	2/20/2019	2/12/2019
190	Closed - Complete	4/29/2019	3/21/2019	3/19/2019
191	Closed - Complete	2/15/2019	1/23/2019	1/22/2019
192	Closed - Complete	4/12/2019	3/14/2019	2/25/2019
193	Closed - Complete	4/17/2019	3/27/2019	3/26/2019
194	Closed - Complete	3/12/2019	2/25/2019	2/25/2019
195	Closed - Complete	3/12/2019	3/1/2019	3/1/2019
196	Closed - Complete	3/12/2019	3/7/2019	3/7/2019
197	Closed - Complete	2/18/2019	1/22/2019	1/21/2019
198	Closed - Complete	3/11/2019	3/8/2019	3/8/2019
199	Closed - Complete	3/22/2019	2/28/2019	2/28/2019
200	Closed - Complete	3/11/2019	2/18/2019	2/18/2019
201	Closed - Complete	3/12/2019	3/12/2019	3/12/2019
202	Closed - Complete	3/26/2019	2/26/2019	2/26/2019
203	Closed - Complete	3/26/2019	3/7/2019	2/27/2019
204	Closed - Complete	3/8/2019	2/11/2019	1/16/2019
205	Closed - Complete	3/20/2019	2/28/2019	2/26/2019
206	Closed - Complete	1/9/2019	1/8/2019	10/8/2018
207	Closed - Complete	2/22/2019	1/30/2019	1/29/2019
208	Closed - Complete	3/28/2019	3/20/2019	3/20/2019
209	Closed - Complete	3/6/2019	2/8/2019	2/8/2019
210	Closed - Complete	1/4/2019	1/4/2019	1/4/2019
211	Closed - Complete	3/11/2019	2/14/2019	2/13/2019
212	Closed - Complete	1/14/2019	1/14/2019	1/14/2019
213	Closed - Complete	2/20/2019	1/28/2019	12/10/2018
214	Closed - Complete	3/11/2019	2/14/2019	2/13/2019
215	Closed - Complete	3/7/2019	2/14/2019	2/13/2019
216	Closed - Complete	3/7/2019	2/14/2019	2/13/2019
217	Closed - Complete	4/16/2019	2/22/2019	2/20/2019
218	Closed - Complete	4/4/2019	3/14/2019	3/12/2019
219	Closed - Complete	7/26/2019	3/14/2019	3/13/2019
220	Closed - Complete	3/11/2019	2/25/2019	2/25/2019
221	Closed - Complete	3/22/2019	3/8/2019	3/4/2019
222	Closed - Complete	5/30/2019	3/7/2019	3/7/2019
223	Closed - Complete	5/7/2019	2/7/2019	2/5/2019
224	Closed - Complete	2/14/2019	1/24/2019	1/22/2019
225	Closed - Complete	2/20/2019	1/18/2019	1/16/2019
226	Closed - Complete	2/15/2019	1/18/2019	1/16/2019
227	Closed - Complete	3/11/2019	2/22/2019	2/22/2019
228	Closed - Complete	6/6/2019	2/7/2019	2/5/2019
229	Closed - Complete	4/15/2019	3/21/2019	3/19/2019
230	Closed - Complete	4/22/2019	3/29/2019	3/27/2019
231	Closed - Complete	2/18/2019	2/18/2019	2/18/2019
232	Closed - Complete	2/26/2019	2/26/2019	2/26/2019
233	Closed - Complete	1/17/2019	1/17/2019	1/17/2019
234	Closed - Complete	4/16/2019	3/15/2019	3/14/2019
235	Closed - Complete	3/29/2019	3/22/2019	3/21/2019
236	Closed - Complete	2/18/2019	2/18/2019	2/18/2019
237	Closed - Complete	1/15/2019	1/11/2019	1/4/2019
238	Closed - Complete	1/15/2019	1/15/2019	1/7/2019
239	Closed - Complete	3/26/2019	3/13/2019	3/12/2019
240	Closed - Complete	3/8/2019	2/6/2019	2/5/2019
241	Closed - Complete	3/11/2019	3/11/2019	3/11/2019
242	Closed - Complete	3/29/2019	3/15/2019	3/14/2019
243	Closed - Complete	4/4/2019	3/20/2019	3/19/2019
244	Closed - Complete	1/22/2019	1/22/2019	11/14/2018
245	Closed - Complete	3/15/2019	3/14/2019	3/7/2019
246	Closed - Complete	3/26/2019	3/20/2019	3/12/2019
247	Closed - Complete	2/4/2019	2/4/2019	2/4/2019
248	Closed - Complete	2/5/2019	2/5/2019	2/5/2019
249	Closed - Complete	2/5/2019	2/5/2019	2/5/2019
250	Closed - Complete	4/4/2019	3/28/2019	3/26/2019
251	Closed - Complete	3/4/2019	2/28/2019	2/27/2019
252	Closed - Complete	2/15/2019	2/14/2019	2/12/2019
253	Closed - Complete	3/12/2019	3/10/2019	3/9/2019
254	Closed - Complete	5/1/2019	2/18/2019	2/18/2019
255	Closed - Complete	2/13/2020	3/26/2019	1/21/2019
256	Closed - Complete	3/12/2019	3/11/2019	3/7/2019

[Ashish_Mathur]

Hi,

I do not understand your requirement at all.  On a smaller dataset, could you show the expected result in a simple Table format.