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

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.

Reply
Anonymous
Not applicable

Counting matches within a table based on two other columns

Hi All,

 

I have a table of history data (one row per event) that looks like:

PBI Table Search.png

 

The _primarykey here is actually that of a parent table, not the history table... the parent item can have multiple rows in the history table.

 

What I'm looking for is the DAX to populate the "Renewed" column, based on finding another row (for the same parent primary key) that has a 'Start Date' that matches the 'End Date' of the current row.

 

I've tried the following as a calculated column, but i get a syntax error:

 

Renewed =
VAR vkey = history[_primarykey]
VAR vdate = history[End Date]
RETURN
    IF (
        COUNTROWS (
            FILTER (
                FILTER ( FILTER ( history, ALL ), history[_primarykey] = vkey ),
                history[Start Date] = vdate
            )
        )
            > 0,
        "Yes",
        "No"
    )

 

The logic being to expand the table to ALL rows, then filter back down to match the (parent) primary key, and then the dates, and to count the rows of that table... if the count is greater than zero then 'Renewed' = "Yes".

 

All help appreciated.

1 ACCEPTED SOLUTION

@Anonymous

 

This Column should work as well

 

Renewed =
VAR myenddate = Table1[End Date]
RETURN
    IF (
        CONTAINS (
            FILTER ( Table1, Table1[_primarykey] = EARLIER ( Table1[_primarykey] ) ),
            Table1[Start Date], myenddate
        ),
        "Yes",
        "No"
    )

Regards
Zubair

Please try my custom visuals

View solution in original post

5 REPLIES 5
Greg_Deckler
Super User
Super User

Why not just:

 

Renewed Column = IF([EndDate] IN ALL(Matches[StartDate]),"Yes","No")

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Hi @Greg_Deckler... well I tried to do it with table filters and counts because I hadn't yet come across the IN operator...

 

The solution suggested doesn't take into account the match on the parent _primarykey also though... I need to match two items between the rows. Does IN handle multiple columns?

OK, try this then:

 

Renewed = IF([EndDate] IN FILTER(ALL(Matches[StartDate]),[_primarykey]=EARLIER([_primarykey])),"Yes","No")

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@Anonymous

 

This Column should work as well

 

Renewed =
VAR myenddate = Table1[End Date]
RETURN
    IF (
        CONTAINS (
            FILTER ( Table1, Table1[_primarykey] = EARLIER ( Table1[_primarykey] ) ),
            Table1[Start Date], myenddate
        ),
        "Yes",
        "No"
    )

Regards
Zubair

Please try my custom visuals

@Anonymous

 

counting.png


Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors