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

johnt75's avatar
johnt75
Super User
4 years ago

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.