Forum Discussion
Solved
USERELATIONSHIP is not working.
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 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
Hi,
Share some data (in a format the can be pasted in an MS Excel file) and show the expected result.
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]))
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 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 Hi,
I do not understand your requirement at all. On a smaller dataset, could you show the expected result in a simple Table format.