Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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
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
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.
@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 haha
Hi @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
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.
HI @danish169,
>>Im struggling to understand how firstnonblank can return blank values when there is a value in column in each case.
I think they may be related to your conditions first. If no records are suitable for these conditions, firstnonblank function will also return the blank as result.
>>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?
In fact, I just mean the power bi data model tables do not include the index. So firstnonblank function may get a different result that does not match the current data model table sorting orders which you viewed.
Your table create at fields seems stored with sanitized DateTime values, we can get the min 'create at' and use the current 'user id' and variable 'create at' to look up the first 'created by' field values.
formula =
VAR datetimestart =
DATE ( 2022, 02, 22 ) + TIME ( 15, 48, 00 )
VAR timefinish =
DATE ( 2022, 02, 22 ) + TIME ( 16, 03, 00 )
VAR currUser =
SELECTEDVALUE ( 'temp wishlist_stock_alert'[user_id] )
VAR firstNonCreateat =
CALCULATE (
MIN ( 'temp wishlist_stock_alert'[created_at] ),
FILTER (
ALLSELECTED ( 'temp wishlist_stock_alert' ),
[created_at] <= timefinish
&& [created_at] >= datetimestart
&& [created_by] <> BLANK ()
),
VALUES ( 'temp wishlist_stock_alert'[user_id] )
)
RETURN
LOOKUPVALUE (
'temp wishlist_stock_alert'[created_by],
'temp wishlist_stock_alert'[user_id], currUser,
'temp wishlist_stock_alert'[created_at], firstNonCreateat
)
Regards,
Xiaoxin Sheng
Thank you for the response,
My conditions are able to retrieve a response so I dont think so........
In the interim I managed to use @amitchandak solution even in a calculated colum. This is the full code in situ now:
Added Wishlist By Admin =
SELECTCOLUMNS(
topn(1,filter('temp wishlist_stock_alert', 'temp wishlist_stock_alert'[created_at] <= ('temp call_suite_list_calls_by_extension'[DateFinish])
&& 'temp wishlist_stock_alert'[created_at] >=('temp call_suite_list_calls_by_extension'[DateStart]) && NOT(ISBLANK('temp call_suite_list_calls_by_extension'[UserID])) && 'temp wishlist_stock_alert'[created_by] = ('temp call_suite_list_calls_by_extension'[UserID])) ,'temp wishlist_stock_alert'[id], ASC),"@created_by_admin", 'temp wishlist_stock_alert'[created_by])
This works fine and gets the desired result but it is insanely slow and I wonder if there is a way to speed it up. Refreshing the table takes a good 7 or 8 minutes longer.
I will try your solution when I get a second to see if it is quicker.
Thanks you for this.
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
Can anyone help with this one please?
User | Count |
---|---|
41 | |
27 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
19 | |
18 | |
15 |