Getting a List of Values from a Measure Aggregate

[Anonymous]

Hi,

I don't know if this is possible, but I would like to return a list of values from an aggregated measure.

I have a measure called 'Start' that returns 1 or 0 based on some conditions. I use this measure in another measure to calculate the number of contracts that meet this criterion:

Number Start = CALCULATE(DISTINCTCOUNT(Retention[contract_id]), FILTER(Retention, [Start] = 1))

The reason I have this as a measure and not a calculated column, is that the 'Start' measure is based off values in a slicer, so it needs to be dynamic.

I would like to be able to get all the values as a list that make up the DISTINCTCOUNT in the above measure.

The closest I've been able to get is this:

Measure = 
CALCULATE(CONCATENATEX (
    VALUES ( Retention[contract_id] ),
    Retention[contract_id],
    UNICHAR ( 10 ),
    Retention[contract_id]
), FILTER(Retention, [Start] = 1))

But this is all one string, whereas I would like each contract ID on a separate line.

Does anyone know if this is actually possible?

Any help would be much appreciated.

[Ashish_Mathur]

Hi,

As a sidenote, your measure can be simplified to:

Number Start = CALCULATE(DISTINCTCOUNT(Retention[contract_id]),Retention[Start] = 1)

[DataInsights]

@Anonymous,

Try replacing the CONCATENATEX portion of your measure with this. The delimiter is an empty string.

CONCATENATEX ( Retention, Retention[contract_id] & UNICHAR ( 10 ), "" )

[DataInsights]

The delimiter is optional, so you can simplify:

CONCATENATEX ( Retention, Retention[contract_id] & UNICHAR ( 10 ) )