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 Guys,
I am sure that an identical question was already raised, but I am quite new to Power Query Editor, hence I don't really know how to search for the answer. I have tried few solutions, but nothing seem to work (or I cannot really do it).
Anyway: data form SQL server, after some modeling we have:
Column on the left: employee ID (multiple for the same ID)
Column on the right: date (different for each line per ID)
What I would need to have in the 3rd column is minimum date value that appears for the given ID.
Thanks,
Lucjan
Solved! Go to Solution.
Hi @Lucjan_Szulik,
Don't know what you want to calculate but believe that the best way is to use measures because calculated columns have additional space to your model and complexity.
But the options you have for creating the column are DAX and M language see below both options.
DAX Calculated column:
Minimum Date Dax = CALCULATE ( MIN ( Dates_DAX[Date] ); FILTER ( Dates_DAX; Dates_DAX[ID] = EARLIER ( Dates_DAX[ID] ) ) )
M Language:
See below result for both columns and attach PBIX file.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @Lucjan_Szulik,
Can you mark the proper answer as a solution please?
Best Regards,
Dale
Hi @Lucjan_Szulik,
Why fo you need to create a 3rd column?
If you only want to know what is the minimum date for a particular ID when you add it to a visual you can select the sumarization as MINIMUM, other way is to create a measure:
Minimum Date = MIN(Table[begin_valid_date])
And add it to your visuals then based on context if you add the ID's it will return the minimum value of the context given.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi,
Need it for calculating absenteeism per seniority.
I have another table with scheduled hours and all the present/absenteeism. I need to add seniority of every agent on the given day and base on that build some visualizations.
Thanks,
Lucjan
Hi @Lucjan_Szulik,
Don't know what you want to calculate but believe that the best way is to use measures because calculated columns have additional space to your model and complexity.
But the options you have for creating the column are DAX and M language see below both options.
DAX Calculated column:
Minimum Date Dax = CALCULATE ( MIN ( Dates_DAX[Date] ); FILTER ( Dates_DAX; Dates_DAX[ID] = EARLIER ( Dates_DAX[ID] ) ) )
M Language:
See below result for both columns and attach PBIX file.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering 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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |