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 in...
  • AlexisOlson's avatar
    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.