Forum Discussion

Skunny11's avatar
Skunny11
Frequent Visitor
6 years ago
Solved

Measure for string values?

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.

Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    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.

8 Replies

  • AlB's avatar
    AlB
    Community Champion
    6 years ago

    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  Datanaut

    • Skunny11's avatar
      Skunny11
      Frequent Visitor
      6 years ago

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

      • AlB's avatar
        AlB
        Community Champion
        6 years ago

        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  Datanaut