Measure to Column

[martcatnip]

Hi,

I currently have a measure

VAR __SelectedDate = MAX('Log_Activation'[DateTime])

VAR __Table1 = FILTER(ALL('Log_Activation'),[DateTime]<=__SelectedDate)

VAR __Table2 = FILTER(ALL('Log_Activation'),[DateTime]<__SelectedDate)

VAR __Device1 = DISTINCT(SELECTCOLUMNS(__Table1,"Device",[Name]))

VAR __Device2 = DISTINCT(SELECTCOLUMNS(__Table2,"Device",[Name]))

VAR __Table = EXCEPT(__Device1, __Device2)

VAR __Devices = DISTINCT(SELECTCOLUMNS(__Table,"Device",[Device]))

RETURN

CONCATENATEX(__Devices,[Device],",")

I am wondering if there is any way I can change it to a column.

Thanks

[v-stephen-msft · Accepted solution]

Hi @martcatnip ,

As @Greg_Deckler  said, if you want dynamic results, calculated columns are not recommended. If it is a static result, you can modify it to

Calculated Column =
VAR __Table1 =
    FILTER (
        ALL ( 'Log_Activation' ),
        [DateTime] <= EARLIER ( 'Log_Activation'[DateTime] )
    )
VAR __Table2 =
    FILTER (
        ALL ( 'Log_Activation' ),
        [DateTime] < EARLIER ( 'Log_Activation'[DateTime] )
    )
VAR __Device1 =
    DISTINCT ( SELECTCOLUMNS ( __Table1, "Device", [Name] ) )
VAR __Device2 =
    DISTINCT ( SELECTCOLUMNS ( __Table2, "Device", [Name] ) )
VAR __Table =
    EXCEPT ( __Device1, __Device2 )
VAR __Devices =
    DISTINCT ( SELECTCOLUMNS ( __Table, "Device", [Device] ) )
RETURN
    CONCATENATEX ( __Devices, [Device], "," )

The calculated column will use EARLIER to get the current row.

Best Regards,

Stephen Tao

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

[Greg_Deckler]

@martcatnip In your case, no if you want it to remain dynamic based upon user interaction. Calculated columns are only recaculated at the time of data load/refresh. Now, if it doesn't need to be dynamic and your "__SelectedDate" can be based on something like a [Date] column in your table, then just set it to that and in theory the rest of the formula looks like it should be just fine.