DAX measure to return alternative result for total

0
votes

How to create DAX measure returning different value for total in table visual? I would like it for conditional formatting for whatever dimension split in table visuals. But since conditional formatting does not work for totals I do not want to display it for that line.

I need something like:

IF(condition_identifying_total_line, "Alternative result", [TrafficLightIcon])

Edit. This does exactly what I want but I hope for more elegant approach or any other suggestions:

IsTotal =
COUNTROWS(FactTable) =
CALCULATE (
    COUNTROWS ( FactTable ),
    ALLSELECTED ( FactTable)
)

This measure works for whatever dimension split of Sales figures in table visual.

3
powerbidax

3 Answers

1
votes

There are a variety of options depending on exactly what you want to do. I suggest taking a look a the following functions for ideas:

For example, if Sales broken out by a column A, here are a couple possible approaches:

Sales = IF( HASONEVALUE( T[A] ), SUM ( T[Sales] ), <Alternative Result> )

Sales = IF( ISFILTERED ( T[A] ), <Alternative Result>, SUM ( T[Sales] ) )
1
votes

You can find full documentation for how to handle granularities from the SQLBI website here: https://www.daxpatterns.com/handling-different-granularities/

Hope this helps! William

0
votes

I have ended up using INSCOPE function:

IsTotal = NOT(
   ISINSCOPE(products[dimension1])
|| ISINSCOPE(products[dimension2])
|| ISINSCOPE(stores[dimension1])
|| ISINSCOPE(stores[dimension2])
)

Unfortunately it requires hard coding all dimensions by which we want to slice or group visuals.