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.

v-dineshya's avatar
v-dineshya
Community Support
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.