Forum Discussion

Sandhya877's avatar
Sandhya877
Frequent Visitor
2 years ago

Grouping of columns to Create new Column

In a table, I have two fields SubCategory1 and Subcategory1. I am creating a New Column : Category. 

SubCategory1       Subcategory2       quantity 

Accessories           Bike Racks                 5

Accessories           Bike Stands                7

Accessories            Bottels                      4

Accessories           Cleanign Supplies    3

Accessories         Hydration Packs          2

Accessories          Tires                          4

 

Logic: 

SubCategory1 = Accessories and Subcategory2 = Bike Racks and Bike Stands as Bike Accessories

SubCategory1 = Accessories and Subcategory2 = Bike Racks/Bike Stands/Bottels/Cleanign Supplies/Hydration Packs/ Tires as All Accessories

 

When i select All Accessories - I want to include everything under Subcategory2

when i select Bike Accessories - I want only to include Bike Racks and Bike Stands

 

I tried Switch and Nested If functions.  but  its filtering out the data for All Accessories. 

if(SubCategory1 = Accessories && Subcategory2 in {"Bike Racks", "Bike Stands"}, "Bike Accessories", 

If( SubCategory1 = Accessories && Subcategory2 in { Bike Racks/Bike Stands/Bottels/Cleanign Supplies/Hydration Packs/ Tires}, " All Accessories", "ERROR"))

 

Below is the output i am getting: 

Category                  Quantity

Bike Accessories            12 

All Accessories               13

 

I should get 25, but I am getting 13.  

how can i write a dax without the data being filtered out ??

Thank you. 

Need Help
Tips and Tricks

7 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    I would recommend you use either Power Query or the upstream data source for that.  There's no reason to do this in DAX.

    • Sandhya877's avatar
      Sandhya877
      Frequent Visitor
      2 years ago

      I have like thousands of row, i just used sample data. I do not think power query would be better option. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Sandhya877 ,

    I created a sample pbix file(see the attachement), please check if that is what you want.

    1. Create a dimension table as below

    2. Create a measure as below

     

    Measure = 
VAR _category =
    SELECTEDVALUE ( 'Category'[Category] )
RETURN
    SWITCH (
        _category,
        "Bike Accessories",
            CALCULATE (
                SUM ( 'Table'[quantity] ),
                FILTER (
                    'Table',
                    'Table'[SubCategory1] = "Accessories"
                        && 'Table'[Subcategory2] IN { "Bike Racks", "Bike Stands" }
                )
            ),
        "All Accessories",
            CALCULATE (
                SUM ( 'Table'[quantity] ),
                FILTER (
                    'Table',
                    'Table'[SubCategory1] = "Accessories"
                        && 'Table'[Subcategory2]
                        IN {
                            "Bike Racks",
                            "Bike Stands",
                            "Bottels",
                            "Cleanign Supplies",
                            "Hydration Packs",
                            "Tires"
                        }
                )
            )
    )

     

    Best Regards

    • Sandhya877's avatar
      Sandhya877
      Frequent Visitor
      2 years ago

      Thank you. this is helpful, What if the subcategory2 is another related table ?

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi Sandhya877 ,

        Thanks for your feedback. Since the subcategory2 is another related table, could you please provide some raw data in these tables (exclude sensitive data) with Text format and the relation info(cardinality, based field etc) if it created any relationship with the other table. It would be helpful to find out the solution. You can refer the following link to share the required info:

        How to provide sample data in the Power BI Forum

         

        And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

        How to upload PBI in Community

        Best Regards