DAX table creation depending on boolean evaluation

[RickBickens]

Hi there,
I am trying to create a table in PowerBI that is automatically generated depending on the information of a column in another table.
If category has more values, I want to summarize the information of the Master table by category, but if subcategory as more values, I want to use subcategory to summarize the table. 
My DAX code to attempt this table generation is this:

Test Group Dim Lookup = 
var GroupByCat = IF(DISTINCTCOUNT(Item_Master[CATEGORY]) > DISTINCTCOUNT(Item_Master[SUBCATEGORY]), 0, 1)

 
Return
IF(GroupByCat = 0, 
    FILTER(SUMMARIZE(Item_Master,[CATEGORY],"Avg Unit L",AVERAGE(Item_Master[UNIT L]),"Avg Unit W",AVERAGE(Item_Master[UNIT W]),"Avg Unit H", AVERAGE(Item_Master[UNIT H]),"Avg Unit Weight", AVERAGE(Item_Master[UNIT WEIGHT]),"Avg Case L",AVERAGE(Item_Master[MASTER CASE L]),"Avg Case W",AVERAGE(Item_Master[MASTER CASE W]),"Avg Case H",AVERAGE(Item_Master[MASTER CASE H]), "Avg Case Weight",AVERAGE(Item_Master[MASTER CASE WEIGHT]),"Avg Case Qty",ROUNDUP(AVERAGE(Item_Master[UNITS PER CASE]),0)),NOT(ISBLANK([CATEGORY]))), 
    FILTER(SUMMARIZE(Item_Master,[SUBCATEGORY],"Avg Unit L",AVERAGE(Item_Master[UNIT L]),"Avg Unit W",AVERAGE(Item_Master[UNIT W]),"Avg Unit H", AVERAGE(Item_Master[UNIT H]),"Avg Unit Weight", AVERAGE(Item_Master[UNIT WEIGHT]),"Avg Case L",AVERAGE(Item_Master[MASTER CASE L]),"Avg Case W",AVERAGE(Item_Master[MASTER CASE W]),"Avg Case H",AVERAGE(Item_Master[MASTER CASE H]), "Avg Case Weight",AVERAGE(Item_Master[MASTER CASE WEIGHT]),"Avg Case Qty",ROUNDUP(AVERAGE(Item_Master[UNITS PER CASE]),0)),NOT(ISBLANK([SUBCATEGORY]))))
        
   

Unfortunately, both IF and SWITCH only return scalar values and cannot return tables, so that is where I am stuck. With either SWITCH or IF, I get the error"The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

Is there a way to have my new table generated differently based on a boolean evaluation?

[AlexisOlson · Accepted solution]

Instead of using an IF, you can use a UNION and FILTER like this:

Test Group Dim Lookup =
VAR GroupByCat =
    IF (
        DISTINCTCOUNT ( Item_Master[CATEGORY] )
            > DISTINCTCOUNT ( Item_Master[SUBCATEGORY] ),
        0,
        1
    )
VAR _Union_ =
    UNION (
        FILTER (
            SUMMARIZE (
                Item_Master,
                [CATEGORY],
                "Boole", 0,
                "Avg Unit L", AVERAGE ( Item_Master[UNIT L] ),
                "Avg Unit W", AVERAGE ( Item_Master[UNIT W] ),
                "Avg Unit H", AVERAGE ( Item_Master[UNIT H] )
            ),
            NOT ( ISBLANK ( [CATEGORY] ) )
        ),
        FILTER (
            SUMMARIZE (
                Item_Master,
                [SUBCATEGORY],
                "Boole", 1,
                "Avg Unit L", AVERAGE ( Item_Master[UNIT L] ),
                "Avg Unit W", AVERAGE ( Item_Master[UNIT W] ),
                "Avg Unit H", AVERAGE ( Item_Master[UNIT H] )
            ),
            NOT ( ISBLANK ( [SUBCATEGORY] ) )
        )
    )
RETURN
    FILTER ( _Union_, [Boole] = GroupByCat )

I didn't invent this technique myself, but I don't remember where I learned it.

[amitchandak]

@RickBickens ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[RickBickens]

Hi @amitchandak ,

Would screenshots of the tables in question work?
This is the Item_Master table I am pulling information from:

And the Table I am trying to produce from this Item_Master is a lookup table of the average L/W/H for a given Category or Subcategory:

The difference being that I want the output table to automatically generate and summarize based on Category OR Subcategory depending on which one has more resolution. The Item_Masters are always different so having the output table intelligently generate would be ideal.

[amitchandak]

@RickBickens , Please share some sample data. Need to work on the file.

[RickBickens]

@amitchandak I've created a stripped down version of the data to share, but I cannot attach .csv/.xlsx files. I can't share my pbix. file as it has some sensative informaiton in it. Did you want me to just create a table within my reply populated with some sample data?