Grouping of columns to Create new Column

Sandhya877 · ‎10-25-2023

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 · ‎10-27-2023

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 · ‎10-30-2023

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

Anonymous · ‎10-30-2023

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 · ‎10-31-2023

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

Anonymous · ‎10-31-2023

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