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 I

## Min date to be a null value if any of the date value is a null value

Is there a way to calculate a date to be set to null if one of the date is null?

i have close date

A: null

A: 1/2/2012

A: 1/4/2023

If per A has a null close date then the value to be null

7 REPLIES 7
Solution Sage

We probably need some more information, but I'll take a guess at what you are asking for and we can go from there.

I'm assuming your question is assuming data like:

Data =

 Product Date A null A 1/2/2012 A 1/4/2023 B 2/3/2014 B 3/4/2015 C 1/1/1900 C null

And if you want to be able to create a table visual with Table[Product] and [Measure] in the fields such that it looks like:

 Product Measure A null B 2/3/2014 C null

Then the following may be what you want:

``````Measure =
VAR _minDt = MIN( Data[Date] )
VAR _noBlankDts = CALCULATE( ISEMPTY( Data ), ISBLANK( Data[Date] ) )
RETURN
IF( _noBlankDts, _minDt )``````

Helper I

Hi @MarkLaf , the solution did not work for me.

here is my table

ID.       Work.    Close date.     Desired coumn
1         Ab.
1.         Bb.         1/23/2012

1.         Cc.          3/23/2015

2.         Ab.          2/30/2012.     2/30/2012

2.          Ac.          2/23/2015.     2/30/2012

2.          Cc.          5/15/2018.      2/30/2012

if any of close date is empty, I would like the see a measure that would get a minimum value per id (includine null value)

Super User

if you would like to include other columns, try like:

``````measure2 =
IF(
BLANK() IN
CALCULATETABLE(
VALUES(data[CloseDate]),
ALLEXCEPT(data, data[ID])
),
" ",
CALCULATE(
MIN(data[CloseDate]),
ALLEXCEPT(data, data[ID])
)
)``````

it worked like:

Super User

hi @bharukc

not sure if i fully get you, try to plot a table visual the ID column and a measure like:

``````measure =
IF(
BLANK() IN VALUES(data[CloseDate]),
" ",
CALCULATE(
MIN(data[CloseDate]),
data[ID]=MAX(data[ID])
)
)``````

it worked like:

Solution Sage

It's not clear if you want this in a column or measure, but I think this should work in either:

``````Measure/Column =
VAR _earliestRow =
INDEX(
1,
ALL( Data[ID], Data[Close date] ),
ORDERBY( Data[Close date], ASC ),
KEEP,
PARTITIONBY( Data[ID] )
)
RETURN
CALCULATE(
MIN( Data[Close date] ),
REMOVEFILTERS( Data ),
_earliestRow
)``````

Solution Sage

Still figuring out the new window functions a bit.

I realized that INDEX will return a table of multiple rows if it's in a filter context that includes multiple partitions (e.g. this will happen in a total row in a table, subtotals in matrix, etc.).

Not sure if there is a better method, but using INDEX again to select one row from the resulting table of partitions (using same ordering logic from original INDEX) should provide a good summary result as needed:

``````Measure=
VAR _dir = 1 //  Last = 0   First = 1
VAR _dtByPartition =
INDEX(
1, ALL( Data[ID], Data[Close date] ),
ORDERBY( Data[Close date], _dir ), KEEP, PARTITIONBY( Data[ID] )
)
VAR _selectPartition =
INDEX(
1, _dtByPartition,
ORDERBY( Data[Close date], _dir ), KEEP
)
RETURN
SUMMARIZE( _selectPartition, Data[Close date] )``````

Super User

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.