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
Pbiuserr
Post Prodigy
Post Prodigy

Calculate table with two criterias

Hello,

I would like to create a table basic on two criteria - max date for ID, and if date is the same, then max value for the date. If it happens that date and value is the same for all lines for certain ID - just take one line for that ID 

 

My initial table

IDDateValue
ID101.01.2022100
ID101.01.2022150
ID101.02.2022100
ID201.03.2022150
ID201.03.2022100
ID301.04.2022300
ID301.04.2022300
ID301.04.2022300

 

Expected output

IDDateValue
ID101.02.2022100
ID201.03.2022150
ID301.04.2022300

 

Thank you in advance for help

1 ACCEPTED SOLUTION
SpartaBI
Community Champion
Community Champion

@Pbiuserr , create this calculated table:

 

 

 

Table Result = 
VAR _tbl1 = 
ADDCOLUMNS(
    VALUES('Table'[ID]),
    "@MaxDate" , CALCULATE(MAX('Table'[Date]))
)
VAR _tbl2 = 
    ADDCOLUMNS(
        _tbl1,
        "@Value", 
        VAR _id = 'Table'[ID]
        VAR _date = [@MaxDate]
        RETURN    
            CALCULATE(
                MAX('Table'[Value]), 
                'Table'[ID] = _id && 'Table'[Date] = _date 
            )
    )
RETURN
    _tbl2

 

 

Solution PBIX:
Calculate table with two criterias 2022-08-03.pbix


2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png


Full-Logo11.png

SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Showcase Report – Contoso By SpartaBI

View solution in original post

9 REPLIES 9
SpartaBI
Community Champion
Community Champion

@Pbiuserr , create this calculated table:

 

 

 

Table Result = 
VAR _tbl1 = 
ADDCOLUMNS(
    VALUES('Table'[ID]),
    "@MaxDate" , CALCULATE(MAX('Table'[Date]))
)
VAR _tbl2 = 
    ADDCOLUMNS(
        _tbl1,
        "@Value", 
        VAR _id = 'Table'[ID]
        VAR _date = [@MaxDate]
        RETURN    
            CALCULATE(
                MAX('Table'[Value]), 
                'Table'[ID] = _id && 'Table'[Date] = _date 
            )
    )
RETURN
    _tbl2

 

 

Solution PBIX:
Calculate table with two criterias 2022-08-03.pbix


2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png


Full-Logo11.png

SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Showcase Report – Contoso By SpartaBI

Thanks, as always excellent help - my one question is that: In which point I add rest columns from the table? I'd need couple of additional columns from that the 'Table'. Is it even possible in that calculation? I think yes, in the _tbl1, create "name", table[column] for each I want to add?

@Pbiuserr my pleasure 🙂
you can add more columns inside addcolumns like you wrote yes. Try and let me know how it went.
Please don't forget to accept the previous message as a solution for community visibility.

Sure thing, already accepted 🙂 

I struggle to add more columns, because VALUES(ID) is my table, so i need make a table like SUMMARIZE(Table, ID, Other column, Other column)?

@Pbiuserr oh yes, if you meant to start from a combination of columns then yes, 
SUMMARIZE('Table', 'Table'[Column], 'Table'[Other Column], 'Table'[Other Column])
Instead of VALUES('Table'[Column])


2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png


Full-Logo11.png

SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Showcase Report – Contoso By SpartaBI

When I try with summarize, it again gets me many records for one ID

SUMMARIZE(Table,Table[ID], Table[FlagPayCod],Table[TechnicalFlag], "@Max_Date", CALCULATE(MAX(Table[Creation_Date]) ) )

no idea why, technical flag is always the same, same for ID, the FlagPayCod differs from one line to another but it shouldnt populate more lines

@Pbiuserr i suggest open a new question from scratch and share there a sample data with all the relevant columns for your scenario and explain again there the logic required and send me here the link to the new question

I think everything is ok but its data engineering part who messed up 🙂 probably tommorow will get update on that

cool :))

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.