Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello.
How to calculate like below it ?
Solved! Go to 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]))
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 🙂
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
Hope it resolves your issue? Did I answer your question? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!! Linkedin Profile |
@ChoiJunghoon , based on Num column, Create a measure and use this with ID
lastnonblankvalue(Table[Num], Min(Table[Completed Date]))
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]
@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 )
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.
@ChoiJunghoon , Can you share sample data and sample output in table format?
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.
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 🙂
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]))
@ChoiJunghoon use this one.
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.
Hope it resolves your issue? Did I answer your question? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!! Linkedin Profile |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |