Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
danish169
Helper I
Helper I

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"

danish169_0-1646640097245.png

 


Thanks in advance to the pros here. 

1 ACCEPTED 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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

10 REPLIES 10
danish169
Helper I
Helper I

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 

johnt75
Super User
Super User

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.

amitchandak
Super User
Super User

@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:

danish169_0-1646656393214.png

 



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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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,

danish169_0-1646918933272.png


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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Can anyone help with this one please?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors