Forum Discussion

danish169's avatar
danish169
Icon for Helper I rankHelper 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
Icon for Helper I rankHelper I
4 years ago

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. 


Anonymous's avatar
Anonymous
Not applicable
4 years ago

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

  • danish169's avatar
    danish169
    Icon for Helper I rankHelper 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