SUMMARIZE by latest ID based on onether ID

[bejenaru_a]

Hi Everyone, 

I have a table which contains projects and for each project I have different versions. 

I would like to have a summarized table for the latest Version ID.

_MeasureTableCockpitByProject = SUMMARIZE(E0101_Cockpit,E0101_Cockpit[Obj_ID],E0101_Cockpit[Version_ID]
    ,"ByProjectEXKL", SUM(E0101_Cockpit[MF1_GEW_RLMV])
    ,"ByProjectMF2-A", SUM(E0101_Cockpit[UmlageA])
    ,"ByProjectMF2-B", SUM(E0101_Cockpit[UmlageB])
    ,"ByProjectMF2-C", SUM(E0101_Cockpit[UmlageC])
    ,"ByProjectMF2-D", SUM(E0101_Cockpit[UmlageD])
    ,"ByProjectMF2-E", SUM(E0101_Cockpit[UmlageE])
    ,"ByProjectMF2-F", SUM(E0101_Cockpit[UmlageF])
    ,"ByProjectMF2-G", SUM(E0101_Cockpit[UmlageG])
    ,"ByProjectMF2-H", SUM(E0101_Cockpit[UmlageH])
    ,"ByProjectMF2-J", SUM(E0101_Cockpit[UmlageJ])
    ,"ByProjectGEM", SUM(E0101_Cockpit[SuUmlage])
    ,"ByProjectMF", SUM(E0101_Cockpit[MF])
    ,"ByProjectInk. MBF", SUM(E0101_Cockpit[MBF])
    ,"ByProjectZzgl. SoMo", SUM(E0101_Cockpit[SMO])
    ,"ByProjectSt.pl.", SUM(E0101_Cockpit[AnzahlParken])
    )

The table should not show these 2 lines:

Can anyone help?

[Anonymous · Accepted solution]

Hi @bejenaru_a ,

You can create a new table with dax

Origin table :

New table :

DAX:

Table 2 =
SELECTCOLUMNS (
    ADDCOLUMNS (
        SUMMARIZE (
            'Table',
            'Table'[Obj_ID],
            "Versions_ID", MAX ( 'Table'[Versions_ID] )
        ),
        "ByPrjoject-A",
            CALCULATE (
                MAX ( 'Table'[ByProject-A] ),
                'Table'[Versions_ID] = EARLIER ( [Versions_ID] )
            ),
        "ByPrjoject-B",
            CALCULATE (
                MAX ( 'Table'[ByProject-B] ),
                'Table'[Versions_ID] = EARLIER ( [Versions_ID] )
            ),
        "ByPrjoject-C",
            CALCULATE (
                MAX ( 'Table'[ByProject-C] ),
                'Table'[Versions_ID] = EARLIER ( [Versions_ID] )
            ),
        "ByPrjoject-D",
            CALCULATE (
                MAX ( 'Table'[ByProject_D] ),
                'Table'[Versions_ID] = EARLIER ( [Versions_ID] )
            )
    ),
    "Versions_ID", [Versions_ID],
    "Obj_ID", [Obj_ID],
    "ByProject-A", [ByPrjoject-A],
    "ByProject-B", [ByPrjoject-B],
    "ByProject-C", [ByPrjoject-C],
    "ByProject-D", [ByPrjoject-D]
)

If I misunderstand your demands, please feel free to let me know.

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[johnt75]

You could try

Summary Table =
var filterTable = ADDCOLUMNS( VALUES('Table'[Object ID]), "@version", CALCULATE(MAX('Table'[Version ID])))
return CALCULATETABLE( SUMMARIZE( 'Table', all your column definitions),
filterTable)

[bejenaru_a]

Thank you for the suggestion but it does not give me the result i wang

I have done the following: 

1. Create a table which gives me the Obj_ID and latest Version_ID 

2.Create the table which summarizes all the Obj_ID and the Version_ID data. (This is the table i had in my initial question)

3.Create a third table which is the result on NATURALINNERJOIN of table 1 and 2.

It is not an elegant solution but this is how much I know about PowerBI 😞

I did find myself with the issue of mismatch types this is why there is a +0 after Version_ID