Forum Discussion
DAX table creation depending on boolean evaluation
- 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.
Anonymous ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
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.
- amitchandak3 years agoSuper User
Anonymous , Please share some sample data. Need to work on the file.
- Anonymous3 years agoNot applicable
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?