Forum Discussion
Firstnonblank and a filtered table
Hi, tried many variations of the following dax expression. Sometimes I get an error, other times a blank and then the error about cannot find a scalar value. I have experimented with min too.
The variable are examples from real data and the table "wishlist_stock_alert" filters correctly often leaving multiple rows. From this filtered table I am trying to get the "created_by" column as a whole number from the first row regardless if there are multiple rows once filtered.
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),
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())
Example data of filtered table and the desire to get 18727 from the first row out of "created_by"
Thanks in advance to the pros here.
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
10 Replies
danish169 , 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] ))))Ok 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
You could create a measure which would work with filters or slicers, e.g. for each user_id
First created_by =
var startDateTime = DATE(2022, 2, 22) + TIME( 15, 48, 0)
var endDateTime = DATE( 2022, 2, 22) + TIME( 16, 03, 00 )
var result = SELECTCOLUMNS(
TOPN( 1, FILTER( 'temp wishlist_stock_alert', 'temp wishlist_stock_alert'[created_at] >= startDateTime &&
'temp wishlist_stock_alert'[created_at] <= endDateTime &&
NOT ISBLANK('temp wishlist_stock_alert'[created_by]) ),
'temp wishlist_stock_alert'[created_at], ASC,
'temp wishlist_stock_alert'[id], ASC
),
"@created by", 'temp wishlist_stock_alert[created_by]
)Using the [id] column as well as the [created_at] column will guarantee only 1 result if the [id] column is unique.
Can anyone help me here? Im really stuck and have a report to sort for Saturday morning. Would really appreciate some insights if anyone is free
