Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Can anyone help me understand why this throws an error?
Orders = VAR startDate = DATE(2017, 7, 1) VAR endDate = DATE(2018,4,30) VAR invLag = 30 VAR prob = 0.8 RETURN ADDCOLUMNS( FILTER( ADDCOLUMNS(SELECTCOLUMNS(GENERATESERIES(1000, 2000), "Order Number", [Value]), "Date", RANDBETWEEN(startDate, endDate), "Value", RANDBETWEEN(0, 1000) ), WEEKDAY([Date],2) < 6 ), "Week Day", FORMAT([Date], "ddd"), "Inv Date", VAR dif = DATEDIFF([Date], endDate, DAY) VAR dif0 = IF(dif > invLag, 0, dif) VAR threshold = prob - dif0 * prob / invLag RETURN IF(RAND()> threshold, BLANK(), INT([Date] + RANDBETWEEN(0, invLag))) )
Its a stand-alone calculated table. Just do Modelling/New Table and paste this in.
The error seems to be ascociated with confusion over implicit type-casting between Date and Int.
The error is...
I think the problem line is with this line: VAR dif0 = IF(dif > invLag, 0, dif)
Solved! Go to Solution.
Hi @RobertSlattery,
By my research, the problem line should be with this line: VAR dif0 = IF(dif > invLag, 0, dif);
If we repalce dif with DATEDIFF([Date], endDate, DAY) , it will work.
However, if we define the var dif and use the formula VAR dif0 = IF(dif > invLag, 0, dif), it will get error.
This is a issue has reported to the Product Team: CRI 64579879 and will be fixed in May release.
Here is a similar thread you could have a reference.
Best Reagards,
Cherry
Hi @RobertSlattery,
You're right, that problem line is with this line: VAR dif0 = IF(dif > invLag, 0, dif).
For the workaround, I suggest you could create the table with the formula below first.
Orders = VAR startDate = DATE(2017, 7, 1) VAR endDate = DATE(2018,4,30) VAR invLag = 30 VAR prob = 0.8 RETURN ADDCOLUMNS( FILTER( ADDCOLUMNS(SELECTCOLUMNS(GENERATESERIES(1000, 2000), "Order Number", [Value]), "Date", RANDBETWEEN(startDate, endDate), "Value", RANDBETWEEN(0, 1000) ), WEEKDAY([Date],2) < 6 ), "Week Day", FORMAT([Date], "ddd") )
Then create the calculated column.
Diff = VAR startDate = DATE ( 2017, 7, 1 ) VAR endDate = DATE ( 2018, 4, 30 ) VAR invLag = 30 VAR prob = 0.8 VAR dif = DATEDIFF ( [Date], endDate, DAY ) VAR dif0 = IF ( dif > invLag, 0, dif ) VAR threshold = prob - dif0 * prob / invLag RETURN IF ( RAND () > threshold, BLANK (), INT ( [Date] + RANDBETWEEN ( 0, invLag ) ) )
And you will get the table.
Best Regards,
Cherry
Hi Cherry (@v-piga-msft), thanks for the answer but, pleas please PLEASE include an explanaition of the root cause so that we don't have to randomly stumble arround in the dark trying various permutations all the time.
I managed to work arround the problem by adding a column to my date table and then using a lookup in the fact table to access the variable weighting...
Dim Date = ADDCOLUMNS( ADDCOLUMNS(CALENDAR([From Date], [To Date]), "Year", YEAR([Date]), "Date Key", VALUE(FORMAT([Date], "YYYYMMDD")), "FY", VAR m = MONTH([Date]) VAR y = YEAR([Date]) RETURN IF(m > 6, y + 1, y), "DOW", FORMAT([Date], "dddd") ), "Weignt", VAR endDate = [To Date] VAR invLag = [invLag] VAR prob = 0.8 VAR d = DATEDIFF([Date], endDate, DAY) VAR x = IF(d > invLag, invLag, d) RETURN prob - x * prob / invLag )
Orders = VAR startDate = DATE(2017, 7, 1) VAR endDate = [To Date] VAR invLag = [invLag] RETURN FILTER( ADDCOLUMNS( FILTER( ADDCOLUMNS(SELECTCOLUMNS(GENERATESERIES(1000, 10000), "Order Number", [Value]), "Date", RANDBETWEEN(startDate, endDate), "Value", RANDBETWEEN(0, 1000) ), WEEKDAY([Date],2) < 6 ), "Week Day", FORMAT([Date], "ddd"), "Inv Date", VAR d = IF(RAND() < LOOKUPVALUE('Dim Date'[Weignt], 'Dim Date'[Date], [Date]), BLANK(), INT([Date] + RANDBETWEEN(0, invLag))) RETURN d, "Weight", LOOKUPVALUE('Dim Date'[Weignt], 'Dim Date'[Date], [Date]) ), WEEKDAY([Inv Date]) < 6 )
Hi @RobertSlattery,
By my research, the problem line should be with this line: VAR dif0 = IF(dif > invLag, 0, dif);
If we repalce dif with DATEDIFF([Date], endDate, DAY) , it will work.
However, if we define the var dif and use the formula VAR dif0 = IF(dif > invLag, 0, dif), it will get error.
This is a issue has reported to the Product Team: CRI 64579879 and will be fixed in May release.
Here is a similar thread you could have a reference.
Best Reagards,
Cherry
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |