Measure for string values?

[Skunny11]

Is it possible to use a measure to show a list of string values?

I have a table imported that has a Name column and a Date column. I want to create a measure that would list all of the names that exist for a given date.

My final objective is to have the following:

Measure listing all Names for the current month

Measure listing all Names for the previous month

Measure for the Names that are net new Names from the previous month

Measure for a count of those names.

[AlB]

Hi @Skunny11 

A measure can only return scalars, not tables, but you could use

Measure =  CONCATENATEX( DISTINCT( Table1[Name] ) )

to list all names of interest. The result would be a string that would look like this:

"Name1, Name2, Name3, Name4"

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  

[Skunny11]

How would that work with bringing back only the current months records?

[AlB]

For the current month, place this on a card visual:

Measure =
VAR CurrentMonth_ =
    MONTH ( TODAY () )
RETURN
    CONCATENATEX (
        CALCULATETABLE (
            DISTINCT ( Table1[Name] ),
            MONTH ( Table1[Date] ) = CurrentMonth_
        ),
        ", "
    )

Or you could also have a calendar table with a relationship to your fact table, place CalendarT[Month] in the rows of a table visual and use the measure from the first message:

Measure =  CONCATENATEX( DISTINCT( Table1[Name] ) , ", ")

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  

[Skunny11]

That first formula only brings back a bunch of commas and no data in between them.

I should mention that my Date table only has 1 date per month so it shows records on Sept 1st 2019, records for Oct 1st 2019, and so on.

[Skunny11]

Measure_Names_Current_Month = 
CALCULATETABLE
(
    VALUES( TABLE1[Name] ),
    MONTH(MAX(TABLE1[Date])) = MONTH(TODAY())
)

I have tried the following but it doesnt return a list of unique values for the current month.

I know that Month(max(table1[date])) = 10 for october and I know month(today()) = 10 also for october so my problem is around getting the measure to list out a row for each name for the month of october (the current month).

I have also tried it with Var because it throws an error that max cant be used in a true/false but this thows error cannot load visual because a table of multiple values was supplied where a single value was expected:

Measure_Names_Current_Month =
VAR CurrentMonth_ = MONTH(TODAY())
RETURN
CALCULATETABLE
(
    VALUES( Table1[Name] ),
    MONTH(Table1[Date]) = CurrentMonth_
)   

[AlB]

Measure =
VAR CurrentMonth_ =
    MONTH ( TODAY () )
RETURN
    CONCATENATEX (
        CALCULATETABLE (
            DISTINCT ( Table1[Name] ),
            MONTH ( Table1[Date] ) = CurrentMonth_
        ),
        Table1[Name],
        ", "
    )

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  

[Skunny11]

Ah ok gotcha, that seemed to have worked, I am assuming there is no way to get each of these server names into their own row?

Also if I were to try and display the previous months server names I try the followin gbut it returns blank, ive also tried PREVIOUSMONTH as well:

Measure_Names_Previous_Month = 
VAR PreviousMonth_ = DATEADD(Table1[Date], -1, MONTH)
RETURN
    CONCATENATEX 
    (
        CALCULATETABLE 
        (
            DISTINCT ( Table1[Name] ),
            MONTH(Table1[Date]) = PreviousMonth_
        ),
        Table1[Name],
        ", "
    )

[v-lid-msft · Accepted solution]

Hi @Skunny11 ,

If you want to show the name in each rows, we can use the table visual and add the following measures as visual filter to meet your requirement:

IsInThisMonth = 
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        'Table',
        AND (
            [Date] >= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 ),
            [Date]
                < DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + 1, 1 )
        )
    )
)

IsInPreviousMonth =
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        'Table',
        AND (
            [Date] < DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 ),
            [Date]
                >= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, 1 )
        )
    )
)

IsNewNamesFromPreviousMonth =
IF ( AND ( [IsInThisMonth] > 0, [IsInPreviousMonth] = 0 ), 1, 0 )

If it doesn't meet your requirement, Please show the exact expected result based on the Tables above.

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.