Forum Discussion
Anonymous
3 years agoNot applicable
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...
- 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.
AlexisOlson
3 years agoSuper User
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.