Grouping of columns to Create new Column

[Sandhya877]

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. 

[Anonymous]

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]

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

[Anonymous]

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

[Sandhya877]

I am not a super user to upload the pbix file.

[Anonymous]

Hi @Sandhya877 ,

It's OK. You can refer the following link to paste the sample data with Text format in your reply directly. And please share the relation info(cardinality, based field etc) if table which the field [subcategory2] exist in created any relationship with the other table. Thank you.

How to provide sample data in the Power BI Forum

Best Regards

[lbendlin]

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]

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