Forum Discussion

danish169's avatar
danish169
Helper I
4 years ago
Solved

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.    Th...
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

danish169's avatar
danish169
Helper I
4 years ago

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.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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