Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Wayfarer
Helper I
Helper I

Replacing DAX EARLIER Calc Columns with Power Query/M

Hi.  I need some help converting DAX calculated columns using the EARLIER function to Power Query.  Can it be done?

 

This is an example of my scenario.  Relationship is Table A 1:M Table B.

Table B:

Table B Unique IDTable A Unique IDDateA boolean column based on certain text in a description fieldEarliest date at TRUE resulting from expression
11020/10/2019FALSE21/10/2019
21021/10/2019TRUE21/10/2019
31022/10/2019TRUE21/10/2019
41101/12/2019TRUE01/12/2019
51113/12/2019FALSE01/12/2019

 

Can Power Query return the earliest date at which the TRUE occurs for each Table A Unique ID?  E.g. the dates rows with B Unique ID 2 and 4.  I assume this would be added as a column to Table B.  But maybe a better model design would be to create a separate table consisting of the Table A Unique ID and the earliest dates??

 

I have done this with DAX calculated columns and EARLIER function, but I think it's part of the performance problems I am seeing.  So thinking PQ - and perhaps different model design - could do it more efficiently?

 

Some ideas I've seen involve Table.Group, not sure that's the right track.

1 ACCEPTED SOLUTION
v-eachen-msft
Community Support
Community Support

Hi @Wayfarer ,

 

You could use Table.SelectRows() function to keep the "true" data. Then use Table.Group() to get the result.

Here is my result.

1-1.PNG

Here is my test file for your reference ( Merge1 ).

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

4 REPLIES 4
v-eachen-msft
Community Support
Community Support

Hi @Wayfarer ,

 

You could use Table.SelectRows() function to keep the "true" data. Then use Table.Group() to get the result.

Here is my result.

1-1.PNG

Here is my test file for your reference ( Merge1 ).

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.
amitchandak
Super User
Super User

@Wayfarer , the relation between A and B is not clear.  But Try

New column in A = Maxx(filter(A,A[Unique ID] =earlier(A[Unique ID]) && A[Date]<earlier(A[Date]) && A[Status] = True),A[Date])

New column in B = Maxx(filter(B,A[Unique ID] =(B[ID])),A[New column in A ])

mahoneypat
Employee
Employee

This could be done in query (and likely would include a group by step), but I think DAX is the better way to go.  How do you know this is causing a performance problem?  You could compare refresh as is vs. where you delete this column (or comment it out with a much simpler expression (e.g., Column =1 //your current expression).

 

If the difference is significant, please share your DAX expression to see if that could be optimized.

 

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


EDIT:  Clarification on tables.  Table A is like "header" records; Table B is like "action" records that apply to the header.  Table B (actions) contains the unique ID of Table A (header).  So 1 header -> many actions.

 

Hey, thanks for the reply.  Apologies for the wall of text to follow..

 

I thought DAX was probably the way to go as well, to be honest; just in the way I interpret how/what to use DAX for and the EARLIER function in particular.  Interpretation could be wrong, though!

 

You're right, I'll do some further testing.  But I perceived performance issues / inefficient setup because my refreshes are quite slow, which is a subjective, but for this query alone maybe 15 minutes or so.  Size-wise it's 7.7GB retrieved, and I notice the refresh spinning for some time after reaching that 7.7GB, as if it's DAX calculated columns processing for each row going through.   If I do a complete refresh in Desktop, rather than one query at a time, I will sometimes get failures due to RAMM running out - I have 16GB.  On top of refresh time, the "Working on it" after entering some of the DAX formulas sticks around for what seems like a long time.

 

A bit of context:  the "check for earliest (or latest) date" logic I'm doing is applied 5 times, for different metrics.  It's all in aid of calculating time between certain (status) changes on a "header" record.   In SQL world, I'd do this as an aggregation in a subquery or self-join.  Each change is identified by certain text in a text field on the "action" record, there isn't an explicit history table for status changes alone.  So for each separate change I need to provide time information for, there's 5x checks for "first, does this "action" record correspond to a status change"; 5x checks to get the "first/latest date it happened at" (there can be multiple changes back and forth); and then 5x calculations for the date difference.

 

I changed the DAX calulated column returning a boolean for "does this "action" record correspond to a status change" into a Power Query column, and that helped a bit.  Just didn't quite know how to replicate the EARLIER type lookup into Power Query.

 

Here is what I'm using to return the earliest date of an occurrence at a criterion:

 

 

First date at x = 
MINX
(
    FILTER
    (
        <table>
        , <table>[unique id] = EARLIER(<table>[unique id]) && <table>[bool calc column t/f]
    )
    , <table>[date]
)

 

 

 

Then this is the days between changes:

 

 

Days between x and y = 
IF
(
    <table>[First date at x] < <table>[Last date at y]
    , DATEDIFF(<table>[First date at x], <table>[Last date at y], DAY)
    , BLANK()
)

 

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.