Forum Discussion
Convert DAX to M
Hi, I have a calculated column that I have created using the DAX syntax below. This works as expected but I'd really like to have this column created in power query but I'm really struggling with the correct syntax. Would anyone be able to re-write this for me using the custom column option please?
The column is looking for the customers earliest sub date across all their rows of data associated to the ID.
Example output. As you can see, the new column has output the same value across all IDs as that's the customers earliest sub date.
ID. Subdate. Earliest Sub Date
123. 01/01/2014. 01/01/2014
123 01/09/2014. 01/01/2014
123. 01/10/2016. 01/01/2024
123 01/12/2024. 01/01/2024
Earliest sub date =
Var earliestdate =
Calculate(
Min(sp[subdate]),
Filter (sp,sp[id] = earlier (sp[id])))
Var earliestsubdate =
Calculate(
Max(sp[subdate]),
Filter (
sp,
sp[id] = earlier (sp[id]) &&
Sp[sub] = earliestdate ))
Return earliestclosedate
6 Replies
M_SBS_6 Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.Hi M_SBS_6 ,
You can try these in Power query-1. Group By Id (Choose Advanced)
2. 1st consider all rows
3. In 2nd grouping, add min Sub dateThis will give you all the rows with along with sub date and its corresponding Earliest Sub date.
Hope this helps!
If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
