Forum Discussion

martcatnip's avatar
martcatnip
Frequent Visitor
5 years ago
Solved

Measure to Column

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

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    5 years ago

    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.

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    5 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.