Forum Discussion
Summarize table dynamically based on field parameter
Hi as1195
Does Manufacturer > Brand > SubBrand > Variant form a "natural hierarchy"? It appears so from your sample data at least 🙂
That is, does any value at a particular level imply a unique value at the level immediately above?
i.e. each Variant exists under a unique SubBrand, each SubBrand exists under a unique Brand etc?
If it is a natural hierarchy, I believe it is possible and I can put together an example. If it is not a natural hierarchy, you could still adjust the model to create a natural hierarchy behind the scenes.
Regardless, I will put together an example based on what I am thinking and reply soon. The main difficulty is that field parameters are intended to determine fields included in a visual, but not to generate dynamic DAX expressions using those fields.
Hi as1195
I'm not sure if you're still looking for a solution to this, but I have attached a PBIX showing the method I was thinking of.
This is based on the sample dataset from your original post.
1. Model diagram:
2. Product table
Product is a typical product table with a Product Key column added.
For this method to work, the assumption is that Manufacturer>Brand>SubBrand>Variant forms a natural hierarchy.
This means that the "deepest" level of the hierarchy maps 1:1 to the combinations of values on all levels of the hierarchy.
3. ProductAttributeValue table
This is created by unpivoting all columns of Product except Product Key, and adding a Depth column.
ProductAttributeValue[Attribute] is related to 'Product Parameter'[Product Parameter], so that when particular fields are selected, the visible rows of ProductAttributeValue include those levels of the Product hierarchy.
4. Top Attribute Combination measure
This measure assumes you have a Sales Amount measure, and uses the above model to return the result you were looking for:
Top Attribute Combination =
VAR MaxDepthSales =
CALCULATETABLE (
ADDCOLUMNS (
SUMMARIZE (
ProductAttributeValue,
ProductAttributeValue[Value],
ProductAttributeValue[Depth]
),
"@Sales", [Sales Amount]
),
LASTNONBLANK ( ProductAttributeValue[Depth], 0 ) -- Filter on max depth
)
VAR MaxDepthTopAttribute =
INDEX (
1,
MaxDepthSales,
ORDERBY ( [@Sales], DESC, ProductAttributeValue[Value], ASC ) -- break ties lexicographically
)
VAR MaxDepthTopAttributeProducts =
CALCULATETABLE (
VALUES ( ProductAttributeValue[Product Key] ),
MaxDepthTopAttribute
)
VAR AttributeConcatenation =
CALCULATE (
CONCATENATEX (
SUMMARIZE (
ProductAttributeValue,
ProductAttributeValue[Value],
ProductAttributeValue[Depth]
),
ProductAttributeValue[Value],
"-",
ProductAttributeValue[Depth]
),
MaxDepthTopAttributeProducts
)
RETURN
AttributeConcatenation
5. Report example
I realise this requires a little setup, but I can't see any other method of producing this result.
Conditional tables or tables with conditional lineage are not possible, so there is no way to directly map the field parameter selection to column references within DAX.
Does something like this work for you?