cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

FIRSTNONBLANK returning blank with additional filter

I have a measure that is calculating closest store location to a customers address.  There are 2 types of order a customer can place so only some stores are able to take a "Heavy" order.

The DAX works fine if I don't apply the addditional filter:  filter('Fact FBS Store Lats Longs', 'Fact FBS Store Lats Longs'[Offer] = "Heavy") and there are no blanks returned.

However with the additional filter in some values (about 1 / 4) are returned as blank with no obvious pattern.

This Dax returns some blank values (not good)

CALCULATE (
FIRSTNONBLANK( 'Fact FBS Store Lats Longs'[Store Name], 0),
// Arbitrary tie-break
filter('Fact FBS Store Lats Longs', 'Fact FBS Store Lats Longs'[Offer] = "Heavy"),
TOPN (1
,
'Fact FBS Store Lats Longs',
VAR Lat2 = 'Fact FBS Store Lats Longs'[Latitude]
VAR Lng2 = 'Fact FBS Store Lats Longs'[Longitude]
//---- Algorithm here -----
VAR A =
0.5 - COS ( ( Lat2 - Lat1 ) * P ) / 2
+ COS ( Lat1 * P ) * COS ( lat2 * P ) * ( 1 - COS ( ( Lng2 - Lng1 ) * P ) ) / 2
VAR final =
12742 * ASIN ( ( SQRT ( A ) ) )
RETURN
final,
ASC
)
)

This DAX returns no blank values (good)

FIRSTNONBLANK ( 'Fact FBS Store Lats Longs'[Store Name], 0),
// Arbitrary tie-break
TOPN (
1,
'Fact FBS Store Lats Longs',
VAR Lat2 = 'Fact FBS Store Lats Longs'[Latitude]
VAR Lng2 = 'Fact FBS Store Lats Longs'[Longitude]
//---- Algorithm here -----
VAR A =
0.5 - COS ( ( Lat2 - Lat1 ) * P ) / 2
+ COS ( Lat1 * P ) * COS ( lat2 * P ) * ( 1 - COS ( ( Lng2 - Lng1 ) * P ) ) / 2
VAR final =
12742 * ASIN ( ( SQRT ( A ) ) )
RETURN
final,
ASC
))
0 REPLIES 0