[DAX] create measure value that it doesn't has null value in the column?

[ChoiJunghoon]

Hello. 

How to calculate like below it ?  

Min = CALCULATE(MIN('Table'[CompletedDate]),FIlter('Table','Table'[CompletedDate]<>blank()))

[Anonymous · Accepted solution]

Add another column called "complete" add measure complete =if(isblank(completed date),1,0) 

then create a measure like this 

if(calculate(sum([complete]),filter([table name],[ID] = [ID])> 0, "incompleted" , calculate(min(date),filter(table name],[ID] = [ID]))

[pranit828]

Hi @ChoiJunghoon 

Use the below measure

VAR Min_dt = FORMAT(MINX('Table',DATEVALUE(IF('Table'[CompletedDate]="","1947-08-15",'Table'[CompletedDate]))),"YYYY-MM-DD")
RETURN IF(Min_dt = "1947-08-15","",Min_dt)

 I created this measure and used it in a matrix chart, Here is the o/p.

for I/P

[amitchandak]

@ChoiJunghoon , based on Num column, Create a measure and use this with ID

lastnonblankvalue(Table[Num], Min(Table[Completed Date]))

[ChoiJunghoon]

 Sorry, your Dax is 

ID	Measure
A	2020-08-10
B	2020-07-31
C
D	2020-0803

I want that A is display with "Null"....

Because A has Null in table[CompleteDate] 

[amitchandak]

@ChoiJunghoon , Try this

Measure =
VAR __id = MAX ( 'Table'[ID] )
VAR __date = CALCULATE ( MAX( 'Table'[NUM] ), ALLSELECTED ( 'Table' ), 'Table'[ID] = __id )
CALCULATE ( Min ( 'Table'[Date] ), VALUES ( 'Table'[ID ), 'Table'[ID] = __id, 'Table'[NUM] = __date )

[ChoiJunghoon]

Thank you for your reply. 

My question is, if there is no date value, then that id completed date is null,

and if that id has all date values, i want to load the min completed date among them.

Sorry for your confuse. 

[amitchandak]

@ChoiJunghoon , Can you share sample data and sample output in table format?

[ChoiJunghoon]

Hi, i don't know how to attached file at this..

ID	Unit	CompletedDate
A	1	2020/8/10
A	2
A	3	2020/8/1
B	3	2020/7/31
B	4	2020/7/28
B	5	2020/7/29
B	6	2020/7/27
C	1
C	3
D	1	2020/8/2
D	2	2020/8/1
D	4	2020/8/3
E	1	2020/8/15
E	5
F	1	2020/8/16
F	2	2020/8/15

Create meaure

Table	Min CompletedDate
A
B	2020-07-27
C
D	2020-08-01
E
F	2020-08-15

Example ) A is null, becuase A has null value at completed date column.

B is "2020/7/27", Because B has all value at complted date column.

[nandukrishnavs · Accepted solution]

@ChoiJunghoon 

Try this DAX measure.

MinCompleteDate = 
VAR __result =
    IF (
        BLANK () IN VALUES ( 'Table'[CompletedDate] ),
        "",
        MIN ( 'Table'[CompletedDate] )
    )
RETURN
    __result

Please check the attached pbix file.

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[Anonymous · Accepted solution]

Add another column called "complete" add measure complete =if(isblank(completed date),1,0) 

then create a measure like this 

if(calculate(sum([complete]),filter([table name],[ID] = [ID])> 0, "incompleted" , calculate(min(date),filter(table name],[ID] = [ID]))

[zwal]

@ChoiJunghoon  use this one.

MinCompletedDate =

IF(

COUNTBLANK('Table'[CompletedDate]) > 0,

BLANK(),

MIN('Table'[CompletedDate])

)

Edit: I am adding some explanation. In Microsoft Documentation, MIN is counting BLANK values as 0 and will be included. When I test date with string values, MIN is taking count BLANK values and works. When it changes to date data type, then MIN no longer counts BLANK. I think your scenario is as belows: IF MIN(values) HAS BLANK THEN BLANK ELSE MIN(values).

[pranit828]

Hi @ChoiJunghoon 

Here you go, exactly as per the input you have shared.

Measure =
VAR Min_dt = FORMAT(MINX('Table',DATEVALUE(IF('Table'[CompletedDate]="","1947-08-15",'Table'[CompletedDate]))),"YYYY-MM-DD")
RETURN IF(Min_dt = "1947-08-15","",Min_dt)

 Input and Output as needed.