Small multiples with "measure" field parameters not working as expected

[Anonymous]

Hi all,

in PowerBI we have a KPI/metrics which are not technically related. Functionally however there is a relation and we would like to represent them as so. Instead of building additional tables or transforming the underlying the tables within the underlying database, we would like to make use of field parameters and additional fields to be able to easily select specific KPI's or present them in a certain way.

Example

There are 5 different sales metrics based on multiple fact tables. We would like to label these measures in order to select them in slicers, but also to visualize them in visual with small multiples. The five measures in this example are:

• Sales fixed
• Sales mobile
• Sales security
• Sales workplace
• Sales hardware

These measures are then used in a field parameters and an additional "Portfolio label" is assigned to each measure, as follows:

Sales metrics = {
    ("Fixed sales", NAMEOF('Sales'[Fixed sales]), 0, "Portfolio 1"),
    ("Hardware sales", NAMEOF('Sales'[Hardware sales]), 1, "Portfolio 2"),
    ("Mobile sales", NAMEOF('Sales'[Mobile sales]), 2, "Portfolio 3"),
    ("Security sales", NAMEOF('Sales'[Security sales]), 3, "Portfolio 2"),
    ("Workplace sales", NAMEOF('Sales'[Workplace sales]), 4, "Portfolio 1")
}

Expected result

When this parameter is selected in a clustred bar chart and the Portfolio label is used for small multiples, then we would expect a visual with '3 small multiples', each showing only the measures labled accordingly. So we would expect in the visual that:

• the first small multiple (Portfolio 1) only showing [Fixed sales] and [Workplace sales]
• the second small multiple (Portfolio 2) only showing [Hardware sales] and [Security sales]
• the third small multiple (Portfolio 3) only showing [Mobile sales]

Problem

However we see all measure in each small multiple; so the field Portfolio does not filter the measure within the parameter within the visual. However if we use Portfolio as a slicer and only select Portfolio 1; then we do see only [Fixed sales] and [Workplace sales]

See below picture.

Additional info

We understand that this problem can be solved in other ways; like creating a dimension for all the facts, or creating a single fact from all the sales measures. But that is not the solution for this particular problem. By using field parameters as shown above, we are able to cluster/relate different KPI's and create hierarchies etc. So you should see above solution not as a way to replace star-model way of building a model, but rather to easily enrich a simple KPI table by adding additional lables etc. Another solution is to not use small multiples but rather use duplicated visuals with applied filters, but that would make the report static.

Anyway; we are curious whether our expectation of how it should work is incorrect and the funtionality of small multiples and 'measure' field parameters are working as designed; or whether this is an actual problem.

Also we are curious wether there is a solution.

Many thanks.

Bilal Bouri

[amitchandak]

@Anonymous , Based on what I got, you need small multiple of measure, for that you can use calculation groups. Now you can create a calculation group in Power BI Desktop in the model view 

Calculation Groups - Use Measure with small multiple : https://www.youtube.com/watch?v=LEn6ZnScMZc

Model explorer public preview with calculation group authoring| Measure Slicer: https://youtu.be/VfxfJJ0RzvU

[Anonymous]

@amitchandak thank you for the quick reply. This does not fully solve my question. With calculation groups i can indeed select different measure using a 'single measure' and also use it for small multiples. However, I can not categorize the measures; except if i create multiple calculation groups and create a relationship between them.

With field parameters you can also put different measures within a single parameter, ánd you can additionaly add columns to label the measures. For example in this video: https://www.youtube.com/watch?v=LEn6ZnScMZc:

• By using field parameters you could categorize the measures under atuals and targets for example.
• You could then also select all actual/target measures at once using a slicer

• Example:

Measureset parameter = {
    (COGS, NAMEOF('_MEASURES'[COGS]), 0, "Actual"),
    (COGS, NAMEOF('_MEASURES'[COGS]), 0, "Target"),
    (Gross, NAMEOF('_MEASURES'[COGS]), 0, "Actual"),
    (Gross, NAMEOF('_MEASURES'[COGS]), 0, "Target"),
    (Net, NAMEOF('_MEASURES'[COGS]), 0, "Actual"),
    (Net, NAMEOF('_MEASURES'[COGS]), 0, "Target"),
}

The problem however, is that once you use it as small multiple; then it doesn't work. So I am not sure whether it is a bug or not.

Unfortunately i am not able to add an attachment. See this video for context: https://www.youtube.com/watch?v=5G_xSJy5muo&t=186s