How do beastmodes handle filter context?

NateBI

Please can someone explain what happens under the hood when we slice beast modes (BMs) by dataset fields and/or when a filter is applied to BMs (categorical field filters/date filters)

BMs using SUM() or COUNT( DISTINCT() ) when sliced or filtered don't behave as expected. (expectations being the issue). In one case, I had duplicates so used COUNT(DISTINCT) and sliced this BM with a categorical field. The field counteracted the distinct function and effectively did a count on the field's values.

The broader issue here is reaching for the right tool. From a DAX reference point, it is easy to forget that calculations usually required a combination of ETL and BM to get the job done.

Hopefully, the responses to this fill in the gaps as to why.

RobSomers

Could you post the COUNT(DISTINCT) beast mode you're using and maybe one or two other examples of beast mode that don't work as expected? This will help us have a better idea of the problem you're running into.

MichelleH

@NateBI Aggregate functions like SUM() and COUNT(DISTINCT) use multiple rows to return a single value. The way that the data is grouped or filtered affects which rows are included in the aggregate. When the data is grouped or filtered by a dimension, then the aggregate value for each category will be evaluated using only rows that have the same value for that dimension.

If your beast modes are not behaving in the way you expect, I recommend first reviewing the rows that are returned when you drill into the card to better understand which rows are being included in the calculation. Then if you still have questions, I second @RobSomers that it would be helpful to post some examples in this thread so we can help come up with a solution.

NateBI

@MichelleH Ok this is helpful, so it applies the filter first (even within a card) then calculates the BM. @RobSomers , sure the one I have to hand is this SUM(Implementing) BM:

The dataset has Distinct IDs . 'Implementing' is a Measure field showing the hours each ID has spent against the Implementing status. The 'Status Name' column is the current Status of the ID which has been filtered to Status = Implementing. (Context: IDs can have values in the 'Implementing' Measure but have Staus Name <> Implementing)

My expectation here would be that SUM(Implementing) would show the same values as the Implementing Measure. This is the case when I add the ID into the table (far right) it displays as expected:

If I remove the Status = Implementing filter from the card - and remove the ID from the table - it will show as the first picture.

MarkSnodgrass

Is there anything in your Sorting properties? This can affect Domo's ability to aggregate and count distinct records.

NateBI

Hi @MamtaBolaki not in this scenario but I have noticed that Sorting properties affect figures. If we could add this to the list of how BMs handle external filters that would be great.

NateBI

Thanks @MichelleH so the filter is applied first, then the BM is calculated. Sure @RobSomers an example with SUM().

Context:

Dataset has distinct IDs, a Measure called 'Implementing' showing the number of hours each ID spent in the Implementing status and a Status Name categorical field showing the current status. (worth noting, the IDs where Status Name <> Implementing can still have hours stored in the Implementing Measure)

When the ID is included in the table the BM behaves as originally expected because it matches the Measure:

Sum with distinct.PNG

@MichelleH I now gather why this wouldn't be the case.

When the ID is removed the BM displays different values to the Measure:

Sum without IDs.PNG

In both cases, there is a filter where Status Name = Implementing.

NateBI

My comment is disappearing post edit apologies

Ok, @MichelleH that is very helpful. @RobSomers sure:

Status Name = Current Status of ID

Implementing = Total Hours spent in Status Implementing

Filter on card: Status Name = Implementing

My expectation here would be that the SUM() BM matches the Implementing Measure.

Despite this, the totals are the same:

There are distinct IDs, when I add these the BM and Measure match:

This scenario has no purpose except to demonstrate the concept.

RobSomers

The key is that your SUM(Implementing) is a multiple of your Implementing column. Whenever you have one column that is not an aggregate and another that is an aggregate, Domo will group the non-aggregated column. So for example, in your data it looks like there is one row with 13 hours, two rows with 42 hours, 3 rows with 24 hours, etc. It groups your data by that column and then gives you the sum of that grouping. When you add in the distinct ID's, it can only group by individual rows and so you get SUM(Implementing) and Implementing being the same.

NateBI

Hi @RobSomers, OK that makes sense. Thanks for explaining