CODEWITHSUNDEEP
powerbipowerquery
Nick
Nick

Reputation: 81

Apply condition in Power Query (Filter earliest date based on each Consol ID) - PowerBI

I would like to apply some condition when do the data cleansing in Power BI Power Query.

PowerBI- Power Query

Only need one date record for each CONSOL ID

Conditions

ETD (DD/MM/YYYY)

Condition as below:

i.) Date with 'Null' shouldn't be chosen as earliest date when there have available date for that CONSOL ID.

For example: C001 with date '1/1/2021' should be selected as earliest date, but C002 doesn't have other available date. In this case, 'Null' can be selected.

ii.) If the CONSOL ID have few transport mode (SEA or AIR ,RAI,ROA), ONLY priorities on the earliest date of 'SEA' or 'AIR' transport mode.

For example: In C003, even the earliest date is 1/1/2021, but we only focus on the date with transport mode 'SEA' or 'AIR'. So earliest date for C003 is 1/3/2021, while C004 is 1/6/2021.

iii.) If there is only one transport mode (Doesn't matter is RAI or ROA), the earliest date will be selected.

For example: '1/1/2021' for C005 and C006.

Here attached with the pbix: https://ufile.io/twdwgzh8

Thanks for your attention.

Any assistance you can provide would be greatly appreciated!

Upvotes: 1

Views: 754

Answers (1)

mkRabbani
mkRabbani

Reputation: 16918

You can try this below Measure

earliest = 

var current_row_consolid = min(your_table_name[ConsolID])
var current_row_transport = min(your_table_name[Transport])

var earliest_sea_air =
CALCULATE(
    min(your_table_name[ETD]),
    FILTER(
        ALLEXCEPT(your_table_name,your_table_name[ConsolID]),
        your_table_name[Transport] = "SEA" || your_table_name[Transport] = "AIR"
    )
)

var earliest_others =
CALCULATE(
    min(your_table_name[ETD]),
    FILTER(
        ALLEXCEPT(your_table_name,your_table_name[ConsolID]),
        your_table_name[Transport] <> "SEA" && your_table_name[Transport] <> "AIR"
    )
)

return
if(
    ISBLANK(earliest_sea_air),
    earliest_others,
    earliest_sea_air
)

Here is the final output-

enter image description here

Upvotes: 0

Related Questions