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

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.

    • Bibiano_Geraldo's avatar
      Bibiano_Geraldo
      Super User
      1 year ago

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

      • Chetan007's avatar
        Chetan007
        Frequent Visitor
        1 year ago

        Bibiano_Geraldo 

        Example = 1

        Example = 2

         

        This is my Data and Output.