Forum Discussion

Chetan007's avatar
Chetan007
Frequent Visitor
1 year ago
Solved

Multiple column with group by

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....

Need Help
Show and Tell
Tips and Tricks
  • v-dineshya's avatar
    v-dineshya
    1 year ago

    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.

5 Replies

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    Super User
    1 year ago

    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's avatar
      Chetan007
      Frequent Visitor
      1 year ago

      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.