Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

DAX table creation depending on boolean evaluation

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?

Need Help
  • AlexisOlson's avatar
    AlexisOlson
    3 years ago

    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.

5 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    3 years ago

    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's avatar
    amitchandak
    Super User
    3 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
        amitchandak
        Super User
        3 years ago

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