Adding Flag (Y) to first row of each Region

[sureshg2498]

Hi All,

We have a requirement to add a flag=Y to only first row of each region as shown below.

Region	Product	Sales	Flag
South	iPhone	200	Y
South	Samsung	300	N
South	Moto	600	N
South	Sony	700	N
East	Samsung	800	Y
East	Moto	200	N
West	Moto	1000	Y
West	Sony	2000	N

Could anyone please help me on it?

Thanks,

Suresh.

@Bibiano_Geraldo @rajendraongole1 @Ritaf1983 @danextian  @lbendlin @Ritaf1983 @bhanu_gautam

[MohamedFowzan1]

Hi @sureshg2498 

Whats the exact logic is it by the sort order, highest sales, lowest sales?

Assuming you create a Sort Column/Index:
Calculated Column:

Flag = 
VAR CurrentRegion = Flag_Table[Region]
VAR CurrentSort = Flag_Table[Sort]
VAR MinSort =
    CALCULATE(
        MIN(Flag_Table[Sort]),
        FILTER(
            ALL(Flag_Table),
            Flag_Table[Region] = CurrentRegion
        )
    )
RETURN
    IF(CurrentSort = MinSort, "Y", "N")

Measure: 

Flag Measure = 
VAR CurrentRegion = SELECTEDVALUE(Flag_Table[Region])
VAR CurrentSort = SELECTEDVALUE(Flag_Table[Sort])
VAR MinSort =
    CALCULATE(
        MIN(Flag_Table[Sort]),
        FILTER(
            ALL(Flag_Table),
            Flag_Table[Region] = CurrentRegion
        )
    )
RETURN
    IF(CurrentSort = MinSort, "Y", "N")

If you want to achieve in Powerquery instead here is the MCode:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs4vLclQ0lHKDMjIz0sFMowMDJRidRASwYm5xaV56UCWMZqMb35JPpAyQ9eQn1cJpMyhwq6JxSUoxligSkBNgVkbnoosamiAKgw12wgsHAsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Region = _t, Product = _t, Sales = _t]),

    #"Grouped Rows" = Table.Group(Source, {"Region"}, {{"Grouped", each _, type table [Region=nullable text, Product=nullable text, Sales=nullable text]}}),

    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "FlagIndex", each Table.AddIndexColumn([Grouped], "FlagIndex", 1, 1)),

    #"Added Flag" = Table.AddColumn(#"Added Custom", "Flagged", each Table.AddColumn([FlagIndex], "Flag", each if [FlagIndex] = 1 then "Y" else "N")),

    #"Expanded Flagged" = Table.ExpandTableColumn(#"Added Flag", "Flagged", {"Product", "Sales", "Flag"}),

    Result = Table.RemoveColumns(#"Expanded Flagged", {"Grouped", "FlagIndex"})
in
    Result

Basically first Group by Region:

Add a custom column:

Add the flag:

Then expand the flag and remove unnecessary columns.

If Resolved Mark as Solution to guide Others!

[sureshg2498]

Thank you for the reply Mohamed. We just need to add the Flag to the first row of each region. There is a custom sort on Product Name. Below is the Products order for each region.Some regions may not have all products.

iPhone

Samsung

Moto

Sony

Whatever the Product is present in the first row just need to add the flag as Y.

[MohamedFowzan1 · Accepted solution]

This should be straight forward
A Calculated column for ranking the Products:

ProductRank = 
SWITCH(
    'Flag_Table'[Product],
    "iPhone", 1,
    "Samsung", 2,
    "Moto", 3,
    "Sony", 4,
    99  
)

If you need to do this using a measure:

Flag Product Measure = 
VAR CurrentRegion = SELECTEDVALUE('Flag_Table'[Region])
VAR CurrentRank = SELECTEDVALUE('Flag_Table'[ProductRank])
VAR MinRankInRegion =
    CALCULATE(
        MIN('Flag_Table'[ProductRank]),
        FILTER(
            ALL('Flag_Table'),
            'Flag_Table'[Region] = CurrentRegion
        )
    )
RETURN
    IF(CurrentRank = MinRankInRegion, "Y", "N")

If you need a calculated column:

Flag Product = 
VAR CurrentRegion = 'Flag_Table'[Region]
VAR CurrentRank = 'Flag_Table'[ProductRank]
VAR MinRankInRegion =
    CALCULATE(
        MIN('Flag_Table'[ProductRank]),
        FILTER(
            ALL('Flag_Table'),
            'Flag_Table'[Region] = CurrentRegion
        )
    )
RETURN
    IF(CurrentRank = MinRankInRegion, "Y", "N")

The Product Ranking Column need not be added to the visual, either the measure or the Calculated column would help you in flagging as required

[sureshg2498]

Thank you so much, Mohamed! Your explanation is incredibly clear and helped me solve the issue right away. I really appreciate the time you took to break it down.