Forum Discussion

NickProp28's avatar
NickProp28
Icon for Post Partisan rankPost Partisan
5 years ago
Solved

SUMMARIZE table

Dear Community,

 

Good Day,

Kindly help me on DAX that I would like to use summarize function which only return first row of each consolnumber into new table.

 

Condition:

  • IsDomestic column not fall into 'Y'

For example: 

C006449 having 3 row, follow the sequence of leg order and condition IsDomestic not in 'Y'. 

LegOrder 2 of Consol number C006449 will be return to Summarize table.

LegOrder 3, will be ignore.

 

If the consol number's all leg order is under 'Y' in IsDomestic. Will not return to summarize table (Example: C002194)

*Highlighted with red color is the consol number and leg will be taken into new table.

 

Here the pbix: https://ufile.io/1emv4egh

 

Appreciated any helps.

Need Help
  • lkalawski's avatar
    lkalawski
    5 years ago

    Hi NickProp28 ,

    You can create calculate column to rank based on the conditions:

    Rank = Rankx(
    FILTER(
        Route, Route[IsDomestic] = "N" &&
        Route[ConsolNumber] = EARLIER(Route[ConsolNumber])),
        Route[LegOrder],,ASC,Dense)

    And then you can use this column to create table visual (filter: IsDomestic = N and Rank = 1) or you can create a New Table by using this code:

    NewTable = 
CALCULATETABLE(
    Route,
FILTER(Route,
Route[IsDomestic] = "N" && Route[Rank] = 1)
)

    New table will have only the required rows.

     



    _______________
    If I helped, please accept the solution and give kudos! 😀

2 Replies

  • lkalawski's avatar
    lkalawski
    Icon for Resident Rockstar rankResident Rockstar
    5 years ago

    Hi NickProp28 ,

    You can create calculate column to rank based on the conditions:

    Rank = Rankx(
    FILTER(
        Route, Route[IsDomestic] = "N" &&
        Route[ConsolNumber] = EARLIER(Route[ConsolNumber])),
        Route[LegOrder],,ASC,Dense)

    And then you can use this column to create table visual (filter: IsDomestic = N and Rank = 1) or you can create a New Table by using this code:

    NewTable = 
CALCULATETABLE(
    Route,
FILTER(Route,
Route[IsDomestic] = "N" && Route[Rank] = 1)
)

    New table will have only the required rows.

     



    _______________
    If I helped, please accept the solution and give kudos! 😀