Multiple column with group by

[Chetan007]

it will use 4 column Compalsary

Condition :1) When Weight is < 30 and Flag = "Y" and no any other Flag  = "Y" then Ans is like 50

2) When Weight is >= 30 and Flag = "Y" then Ans is like 25

3) When Some Weight is <30 and Flag = "Y" and Some Weight >= 30 and Flag = "N" then Ans is like 50

4) When Some Weight is <30 and Flag = "Y" and Some Weight >= 30 and Flag = "Y" then Ans is like 25

Please Give me any Solution....

[Bibiano_Geraldo]

Hi @Chetan007 ,

Is this your desired output?

If yes, please create a new calculated column with bellow dax:

Ans = 
VAR CurrentGroup = 'Table'[Group]
VAR CurrentSubGroup = 'Table'[Sub Group Number]
VAR GroupTable =
    FILTER (
        'Table',
        'Table'[Group] = CurrentGroup &&
        'Table'[Sub Group Number] = CurrentSubGroup
    )
VAR AnyFlagY = 
    CALCULATE (
        COUNTROWS ( GroupTable ),
        GroupTable,
        'Table'[Flag] = "Y"
    )
VAR AllFlagY_WeightLT30 =
    CALCULATE (
        COUNTROWS ( GroupTable ),
        GroupTable,
        'Table'[Flag] = "Y" && 'Table'[Weight] < 30
    ) = AnyFlagY

VAR AnyWeightGTE30_FlagN =
    CALCULATE (
        COUNTROWS ( GroupTable ),
        GroupTable,
        'Table'[Flag] = "N" && 'Table'[Weight] >= 30
    ) > 0

VAR AnyWeightGTE30_FlagY =
    CALCULATE (
        COUNTROWS ( GroupTable ),
        GroupTable,
        'Table'[Flag] = "Y" && 'Table'[Weight] >= 30
    ) > 0

RETURN
SWITCH (
    TRUE(),
    -- Condition 1
    'Table'[Weight] < 30 && 'Table'[Flag] = "Y" && AnyFlagY = 1, 50,
    
    -- Condition 2
    'Table'[Weight] >= 30 && 'Table'[Flag] = "Y", 25,

    -- Condition 3
    'Table'[Weight] < 30 && 'Table'[Flag] = "Y" && AnyWeightGTE30_FlagN, 50,

    -- Condition 4
    'Table'[Weight] < 30 && 'Table'[Flag] = "Y" && AnyWeightGTE30_FlagY, 25,

    -- Default
    50
)

[Chetan007]

Thank you for your Replay. 

But Ans is not this type. I Was require 1 to 5 Row Ans is 25. 

Can You Please Consider Group and Sub Group Number to One Group(1 to 5 Rows).

1.)In this One Group when Weight >= 30 and Flag = "Y" then this Group Ans is like 25. Do Not Check Other Condition. 

2.) When One Group all row Weight  >= 0.30 and Flag = "N" and 1 row weight < 0.30 and Flag = "Y" then in this Group All ans is like  50.

[Bibiano_Geraldo]

Can you share a desired output, based on gived example?

[Chetan007]

@Bibiano_Geraldo 

Example = 1

Example = 2

This is my Data and Output.

[v-dineshya · Accepted solution]

Hi @Chetan007 ,

Thank you for reaching out to the Microsoft Fabric Community forum.

Please follow below steps.

1. Created table(Data) with sample data based on your inputs.

2. Created Calculated column (Output) with below DAX code.

Output =

VAR CurrentGroup = Data[Group]

VAR SubGroup = Data[Sub Group Number]

VAR GroupTable =

    FILTER(

        ALL(Data),

        Data[Group] = CurrentGroup &&

        Data[Sub Group Number] = SubGroup

    )

VAR HasWeightGTE30_Y =

    CALCULATE(

        COUNTROWS(Data),

        FILTER(

            GroupTable,

            Data[Weight] >= 30 && Data[Flag] = "Y"

        )

    ) > 0

VAR HasOnlyOne_LT30_Y =

    CALCULATE(

        COUNTROWS(Data),

        FILTER(

            GroupTable,

            Data[Weight] < 30 && Data[Flag] = "Y"

        )

    ) = 1

VAR AllOthers_GTE30_N =

    CALCULATE(

        COUNTROWS(Data),

        FILTER(

            GroupTable,

            Data[Weight] >= 30 && Data[Flag] = "N"

        )

    ) = COUNTROWS(GroupTable) - 1

RETURN

    IF(

        HasWeightGTE30_Y,

        25,

        IF(

            HasOnlyOne_LT30_Y && AllOthers_GTE30_N,

            50,

            50

        )

    )

3. Dragged all the columns into table visual. please refer output snap and attached PBIX file.

If this information is helpful, please “Accept it as a solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.