topic Re: Why does my DAX measure using first no blan return blank even though the column has no blank values? in DAX Commands and Tips

Why does my DAX measure using first no blan return blank even though the column has no blank values?

Jack007 — Sun, 20 Jul 2025 16:23:35 GMT

I have two fact tables:

Table1 contains shipment info (Order Number, Shipment Value, Ship Date, and a "Shipped During Month End" column with "Yes"/"NO" values).
Order Number Shipment Quantity Shipment Value Ship Date Shipped During Month End
Sales001 1 100 2025/6/30 Yes
Sales001 1 100 2025/6/30 Yes
Sales001 2 100 2025/6/15 NO
Sales002 3 100 2025/6/15 NO
Sales003 4 100 2025/6/15 NO
Table2 contains invoicing info.
Order Number Shipment Quantity Invoiced Value
Sales001 1 100
Sales001 1 120
Sales001 2 100
Sales002 3 100
Sales003 4 100
I also have a lookup table with unique Order Number, used in a visual with the following measures:
1. SUM(Invoiced Value) from Table2
2. SUM(Shipment Value) from Table1ShippedMonthEndFlag :=
  CALCULATE(
  FIRSTNONBLANK('Table1'[Shipped During Month End], 1),
  ALLEXCEPT('LookupTable', 'LookupTable'[Order Number])
  )
  When I add these to a matrix visual (Order Number on rows), the third measure returns blank for most orders, even though Shipped During Month End has no blank values.
  Why is this happening?

Re: Why does my DAX measure using first no blan return blank even though the column has no blank values?

FBergamaschi — Sun, 20 Jul 2025 16:52:29 GMT

A couple of questions

1 - Can you please show the bad result you are getting ? I am unable to reproduce the issue, maybe you should insert more rows in the tables you shared?

2 - why are you using 1 as expression in FIRSTNONBLANK? I mean what are you trying to calculate with the measure ShippedMonthEndFlag?

Best

If this helped, please consider giving kudos and mark as a solution

mein replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

Re: Why does my DAX measure using first no blan return blank even though the column has no blank values?

Jack007 — Sun, 20 Jul 2025 16:53:23 GMT

Order Number	Sum(invoiced value)	Sum(Shipment Vallue)	ShippedMonthEndFlag
Sales001	100	100	Yes
Sales001	120	100
Sales001	100	100	NO
Sales002	100	100	NO
Sales003	100	100	NO
Sales004	100	100	NO
Sales005	100	100	NO
Sales006	100	100	NO
Sales007	100	100	NO
Sales008	100	100	NO

2.I want to know if the the order has made shipment during the end of the month.

Re: Why does my DAX measure using first no blan return blank even though the column has no blank values?

FBergamaschi — Sun, 20 Jul 2025 17:24:14 GMT

1. I cannot replicate your issue so I cannot fix it

What about grouping the column Shipped During Month End (putting it in rows of the matrix)?

If this helped, please consider giving kudos and mark as a solution

me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

Re: Why does my DAX measure using first no blan return blank even though the column has no blank values?

Jack007 — Sun, 20 Jul 2025 18:05:34 GMT

2. SUM(Shipment Value) from Table1

3. ShippedMonthEndFlag :=
CALCULATE(
FIRSTNONBLANK('Table1'[Shipped During Month End], 1),
ALLEXCEPT('LookupTable', 'LookupTable'[Order Number])
) when the visual only has these 2 measurs, it works as intended. when i add this measure

1. SUM(Invoiced Value) from Table2 . th eproblem starts to occur.

Re: Why does my DAX measure using first no blan return blank even though the column has no blank values?

FBergamaschi — Mon, 21 Jul 2025 06:32:09 GMT

I got this result, grouping order numbers, keeping the two measures that SUM Invoice and Ship value and creating this measure

ShippedMonthEndFlag =

VAR NumberShipEOM =

SUMX (

VALUES ( Orders[Order Number] ),

IF (

CALCULATE ( COUNTROWS ( Ship ), Ship[Shipped During Month End] = "Yes" ) > 0,

)

RETURN

IF (

[Ship Value] <> 0

&& ISINSCOPE ( Orders[Order Number] ),

IF ( NumberShipEOM > 0, "Yes", "No" )

)

is this OK?

If this helped, please consider giving kudos and mark as a solution

me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

Re: Why does my DAX measure using first no blan return blank even though the column has no blank values?

v-nmadadi-msft — Thu, 24 Jul 2025 17:02:57 GMT

Hi Jack007

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

Re: Why does my DAX measure using first no blan return blank even though the column has no blank values?

v-nmadadi-msft — Mon, 28 Jul 2025 04:10:42 GMT

Hi Jack007

May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.

Thank you

Re: Why does my DAX measure using first no blan return blank even though the column has no blank values?

v-nmadadi-msft — Thu, 31 Jul 2025 08:53:17 GMT

Hi Jack007

As we haven’t heard back from you, we wanted to kindly follow up to check if the suggestions provided by the community members for the issue worked. Please feel free to contact us if you have any further questions.

Thanks and regards

Re: Why does my DAX measure using first no blan return blank even though the column has no blank values?

AlanP514 — Sun, 03 Aug 2025 16:57:46 GMT

HI Jack007
Please try thisz

ShippedMonthEndFlag :=
CALCULATE(
FIRSTNONBLANK('Table1'[Shipped During Month End], 1),
VALUES('LookupTable'[Order Number])
)
If That Still Doesn’t Work
Here are a few things to check:
Relationship direction: Make sure LookupTable → Table1 is active and single-direction, ideally from lookup to fact.
Cardinality: LookupTable should have unique Order Numbers.
Blanks vs “invisible” blanks: If 'Table1'[Shipped During Month End] exists but is all NO for some orders, FIRSTNONBLANK may still return blank depending on sort or filter context.

Order Number	Shipment Quantity	Shipment Value	Ship Date	Shipped During Month End
Sales001	1	100	2025/6/30	Yes
Sales001	1	100	2025/6/30	Yes
Sales001	2	100	2025/6/15	NO
Sales002	3	100	2025/6/15	NO
Sales003	4	100	2025/6/15	NO

Order Number	Shipment Quantity	Invoiced Value
Sales001	1	100
Sales001	1	120
Sales001	2	100
Sales002	3	100
Sales003	4	100