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

[Jack007]

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?

[AlanP514]

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.

[v-nmadadi-msft]

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

[v-nmadadi-msft]

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

[v-nmadadi-msft]

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.

[FBergamaschi]

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

FB

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

[Jack007]

1.

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. 

[FBergamaschi]

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

2.

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

[Jack007]

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.

[FBergamaschi]

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,

            1

        )

    )

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