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.
Hi Community, need your help to solve a problem. I think is prety simple but I'm not being able to solve it.
My scenario is the next one. I have this table.
Name ID___________Month_______Date
Name1___________Sep_________BLANK
Name1___________Oct_________BLANK
Name1___________Nov________11/01/2017
Name1___________Dec_________12/01/2017
...
Name2___________Sep_________BLANK
Name2___________Oct_________10/01/2017
Name2___________Nov_________11/03/2017
Name2___________Dec_________12/02/2017
I need to extract the first-non-blank date from the Date column for each Name ID, in a column. The final table should looks like:
Name ID________Month_______Date__________________First Date
Name1___________Sep_________BLANK_______________11/01/2017
Name1___________Oct_________BLANK_______________11/01/2017
Name1___________Nov________11/01/2017__________11/01/2017
Name1___________Dec_________12/01/2017__________11/01/2017
...
Name2___________Sep_________BLANK_______________10/01/2017
Name2___________Oct_________10/01/2017__________10/01/2017
Name2___________Nov_________11/03/2017_________10/01/2017
Name2___________Dec_________12/02/2017__________10/01/2017
I tried the FIRSTNONBLANK fuction inside CALCULATE, but it didn't work for me. Maybe I'm using it in the wrong way.
Any advice would be helpful. Really thanks in advance.
Kind regards,
Andy.-
Solved! Go to Solution.
Here is the DAX Formula I used.
FirstDateByName:=CALCULATE(FIRSTDATE(Table2[Date]),ALL(Table2[Month]),ALL(Table2[Date]))
There is probably a better way, but this works. Hope it heloped.
Rich
Hi,
This calculated column formula will work
=CALCULATE(MIN([Date]),FILTER(Data,Data[Name ID]=EARLIER(Data[Name ID])))
Hope this helps.
Hey guys, really thaks for the help! I think both of your ideas can work but, FOR ME, I prefer to avoid using EARLIER fuction. It always brings me problems.
Anyway both are great solutions.
Thanks again.
Kind regards,
Andy.-
Always happy to help (where I can). Gotta love this site and the members!
Hi,
This calculated column formula will work
=CALCULATE(MIN([Date]),FILTER(Data,Data[Name ID]=EARLIER(Data[Name ID])))
Hope this helps.
Here is the DAX Formula I used.
FirstDateByName:=CALCULATE(FIRSTDATE(Table2[Date]),ALL(Table2[Month]),ALL(Table2[Date]))
There is probably a better way, but this works. Hope it heloped.
Rich
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 |
---|---|
112 | |
99 | |
82 | |
70 | |
63 |
User | Count |
---|---|
145 | |
111 | |
104 | |
84 | |
64 |