Forum Discussion
Helper IFirstnonblank and a filtered table
HI danish169,
Here is the formula that I modify based on the expressions that you share, you can try it if the performance improved:
Added Wishlist By Admin = VAR filtered = FILTER ( 'temp wishlist_stock_alert', [created_at] <= 'temp call_suite_list_calls_by_extension'[DateFinish] && [created_at] >= 'temp call_suite_list_calls_by_extension'[DateStart] && [created_by] = 'temp call_suite_list_calls_by_extension'[UserID] ) VAR _id = MINX ( filtered, [id] ) RETURN IF ( 'temp call_suite_list_calls_by_extension'[UserID] <> BLANK (), MINX ( FILTER ( filtered, [id] = _id ), [created_by] ) )Regards,
Xiaoxin Sheng
Super Userdanish169 , This is a new calculated column, this will not take slicer value
Try like
Added Wishlist By Admin =
var datetimestart = date(2022,02,22) + time(15,48,00)
var timefinish = date(2022,02,22) + time (16,03,00)
return
calculate(FIRSTNONBLANK('temp wishlist_stock_alert'[created_by],1),
filter('temp wishlist_stock_alert','temp wishlist_stock_alert'[created_at] <= timefinish && 'temp wishlist_stock_alert'[created_at] >= datetimestart && not(Isblank('temp wishlist_stock_alert'[created_by] ))))
danish169Ok cool, a calculated column would work best. Just tried your code and got the followign error:
Now ive seen this before with "not(isblank(.....)" which is why i used "<> BLANK()" in my original code to avoid it.
Feels super close yet so far hahaHi danish169,
Current power bi does not support creating dynamic calendar columns/tables based on filters, they are working on different levels and you can't use the child level to affect its parent. For this scenario, you can use the measure formula instead.
In addition, did your table include fields that store unique and sanitized values that can work as Index? (e.g. number, DateTime)
Since the power bi data model table does not include row and column index, you may need to use it to find out the first index first or the expression will get the min 'create by' number from the corresponding DateTime ranges instead of getting the first non blank records.Notice:
1. the data level of power bi.
Database(external) -> query table(query, custom function, query parameters) -> data model table(table, calculate column/table) -> data view with virtual tables(measure, visual, filter, slicer)
2. If your table does not include Index fields, you can also try to add an index on the query editor side.
Add an index column (Power Query) (microsoft.com)
Regards,
Xiaoxin Sheng- danish169
Hello, Sorry I really dont understand your response and Ive read it a few times. I am not trying to create a dynamic calendar column just trying to insert a whole number. Im just using datetime as a value in which to filter. For example, I can filter the table no problem as you can see here where I create a new table,
All I am trying to do is grab one of the "18727" values from "created_by" and put it into my caluclated column. I have also tried to use "min" but still get errors.
calculate(FIRSTNONBLANK('temp wishlist_stock_alert'[created_by],1),
all('temp wishlist_stock_alert'),
filter('temp wishlist_stock_alert','temp wishlist_stock_alert'[created_at] <= timefinish && 'temp wishlist_stock_alert'[created_at] >= datetimestart),'temp wishlist_stock_alert'[created_by] <> BLANK())
Im struggling to understand how firstnonblank can return blank values when there is a value in column in each case. Does first non blank only work on a unique and single row or a table with an index, to establish the first row, is this what you are saying? There is only ever one value in "created by" that I want to get anyway so it doesnt matter which one is picked. I can change the method if you can suggest one for a calculated column.