How to write a DAX measure that calculates differently depending on data in another column

[obera]

Hi all, hoping you can help me generally or point me in the right direction to do my own research.

I need a DAX measure that will run an aggregation through one of two series of computations depending on a value in a column of the base data, and then return a string. I'm performing an aggregation function first (in this case AVERAGE) then doing additional calculations with two different possible outputs - but I can't figure out how to return a scalar value that the measure can read. I started with just writing the two different calculations to variables and performing both on the overall average, but then couldn't figure out an IF statement output because the IF statement wouldn't be referencing a single value. I tried making a table within the measure using SUMMARIZE and referencing that instead but that's not working either (that might be a stupid thing to try, I don't know, I'm not very experienced with DAX).

This, of course, all works fine if I'm making an actual summary table, but summary tables don't respond to slicers, so I need a measure.

My actual scenario is quite complicated and involves hundreds of thousands of rows of data, so I will give a simplified example scenario. I DON'T need an expanation of how to do each calculation, I can do that myself and if I make two separate measures the outputs are correct. My example below is not even exactly what I'm trying to do. Again, I'm just hoping for some general guidance on how to approach this situation.

Example Scenario:

If Candy Type = Snickers or Mars, then perform this calculation: Average the Number of Candies, then round down to the nearest whole number, then format number as a string.

If Candy Type = KitKat, Milky Way, or Skittles, then perform this calculation: Average all values, then divide by two, then round down to the nearest whole number, then format number as a string.

Output is able to be sliced by Candy Type or other value from the base table while retaining the calculations.

Example Data:

Candy Type	Number of Candies
Snickers	13
Snickers	19
Mars	45
KitKat	60
KitKat	62
Milky Way	68
Milky Way	79
Skittles	91
Skittles	100

Example Desired Output (Matrix Visual that can be sliced and filtered):

Candy Type	Calculated Output
Snickers	16 candies
Mars	45 candies
KitKat	30 candies
Milky Way	36 candies
Skittles	47 candies

[obera]

Error: "A single value for column [Candy Type] in table 'Candies' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

I get this error no matter which suggested method I use, @rsbin and @Ashish_Mathur, which is frustrating because they both seem like they should work, and if I eliminate the "IF" or the "SWITCH" and do each calculation separately I don't get this error. No matter what I try I seem to get this error, but only when I'm combining the two expressions...

I was thinking the issue was my methodology, but maybe it's deeper than that, so here's more specifically what I'm doing in hopes you can help me figure it out:

Value	Type	Duration (in Power Query, this is a duration type column, which DAX sees as a decimal in days)	DAX Duration (converted to a decimal for days)
Critical	A	00:01:59	0.08
Critical	A	00:05:01	0.21
High	A	00:08:47	0.37
High	A	00:07:02	0.29
Moderate	B	00:12:49	0.53
Moderate	B	00:09:22	0.39
Low	B	01:10:27	1.44
Low	B	01:03:30	1.15

If Value = Critical or High, then I need to do the following calculation:

FORMAT ( CALCULATE (
        INT ( AVERAGE ( 'Table'[duration] ) ),
        'Table'[type] = "A"), "00" ) 
& ":" & 
FORMAT ( CALCULATE (
         INT ( ( AVERAGE ( 'Table'[duration] ) - INT ( AVERAGE ( ‘Table’[duration] ) )
                   ) * 24 ),
         ‘Table’[type] = “A” ), "00" ) 
& ":" & 
FORMAT ( CALCULATE (
        ROUND ( ( ( ( AVERAGE ( ‘Table’[duration] ) - INT ( AVERAGE ( ‘Table’[duration] ) )
                              ) * 24 ) 
                           - INT ( ( AVERAGE ( ‘Table’[duration] ) - INT ( AVERAGE ( ‘Table’[duration] ) )
                                     ) * 24 )
          ) * 60, 0 ),
        ‘Table’[type] = “A”), "00")

If Value = Moderate or Low, then I need to do the following calculation:

FORMAT (
    CALCULATE (
        INT ( ( AVERAGE ( 'Table'[duration] ) * 24 ) / 9 ),
        'Table'[type] = "B"
    ),
    "00"
) 
& ":" & 
FORMAT (
    CALCULATE (
        INT (
            (
                ( AVERAGE ( 'Table'[duration] ) * 24 ) / 9
                    - INT ( ( AVERAGE ( 'Table'[duration] ) * 24 ) / 9 )
            ) * 9
        ),
        'Table'[type] = "B"
    ),
    "00"
) 
& ":" & 
FORMAT (
    CALCULATE (
        ROUND (
            (
                (
                    ( AVERAGE ( 'Table'[duration] ) * 24 ) / 9
                        - INT ( ( AVERAGE ( 'Table'[duration] ) * 24 ) / 9 )
                ) * 9
                    - INT (
                        (
                            ( AVERAGE ( 'Table'[duration] ) * 24 ) / 9
                                - INT ( ( AVERAGE ( 'Table'[duration] ) * 24 ) / 9 )
                        ) * 9
                    )
            ) * 60,
            0
        ),
        'Table'[type] = "B"
    ),
    "00"
)

My expected result would then be:

Value	Average Duration
Critical	00:03:29
High	00:07:55
Moderate	01:02:02
Low	03:04:05

[v-yanjiang-msft · Accepted solution]

Hi @obera ,

According to your description, here's my solution.

Create a measure.

Measure =
VAR _A =
    FORMAT (
        CALCULATE ( INT ( AVERAGE ( 'Table'[duration] ) ), 'Table'[type] = "A" ),
        "00"
    ) & ":"
        & FORMAT (
            CALCULATE (
                INT (
                    ( AVERAGE ( 'Table'[duration] ) - INT ( AVERAGE ( 'Table'[duration] ) ) ) * 24
                ),
                'Table'[type] = "A"
            ),
            "00"
        ) & ":"
        & FORMAT (
            CALCULATE (
                ROUND (
                    (
                        (
                            ( AVERAGE ( 'Table'[duration] ) - INT ( AVERAGE ( 'Table'[duration] ) ) ) * 24
                        )
                            - INT (
                                ( AVERAGE ( 'Table'[duration] ) - INT ( AVERAGE ( 'Table'[duration] ) ) ) * 24
                            )
                    ) * 60,
                    0
                ),
                'Table'[type] = "A"
            ),
            "00"
        )
VAR _B =
    FORMAT (
        CALCULATE (
            INT ( ( AVERAGE ( 'Table'[duration] ) * 24 ) / 9 ),
            'Table'[type] = "B"
        ),
        "00"
    ) & ":"
        & FORMAT (
            CALCULATE (
                INT (
                    (
                        ( AVERAGE ( 'Table'[duration] ) * 24 ) / 9
                            - INT ( ( AVERAGE ( 'Table'[duration] ) * 24 ) / 9 )
                    ) * 9
                ),
                'Table'[type] = "B"
            ),
            "00"
        ) & ":"
        & FORMAT (
            CALCULATE (
                ROUND (
                    (
                        (
                            ( AVERAGE ( 'Table'[duration] ) * 24 ) / 9
                                - INT ( ( AVERAGE ( 'Table'[duration] ) * 24 ) / 9 )
                        ) * 9
                            - INT (
                                (
                                    ( AVERAGE ( 'Table'[duration] ) * 24 ) / 9
                                        - INT ( ( AVERAGE ( 'Table'[duration] ) * 24 ) / 9 )
                                ) * 9
                            )
                    ) * 60,
                    0
                ),
                'Table'[type] = "B"
            ),
            "00"
        )
RETURN
    IF ( MAX ( 'Table'[type] ) = "A", _A, _B )

Get the expected result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

This measure works

Measure = if(or(MAX(Data[Candy Type])="Snickers",MAX(Data[Candy Type])="Mars"),int(AVERAGE(Data[Number of Candies])),int(DIVIDE(AVERAGE(Data[Number of Candies]),2)))&" candies"

Hope this helps.

[rsbin]

@obera ,

I would use the SWITCH function:

CandiesResult = SWITCH(
                        TRUE(),
                        MAX( Candies[Candy Type] ) = "Snickers",
                        CONCATENATE( ROUNDDOWN( CALCULATE( AVERAGE(Candies[Number of Candies] ), FILTER( Candies, Candies[Candy Type] = "Snickers")), 0), "Candies"),
                        MAX( Candies[Candy Type] ) = "Mars",
                        CONCATENATE( ROUNDDOWN( CALCULATE( AVERAGE(Candies[Number of Candies] ), FILTER( Candies, Candies[Candy Type] = "Mars")), 0), "Candies"),
                        "xxx" )

I get the following result:

I hope you are able to follow the same pattern for your other "Candies".

Good Luck and Regards,