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...
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.

AlB's avatar
AlB
Icon for Community Champion rankCommunity 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
      Icon for Community Champion rankCommunity 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

      • Skunny11's avatar
        Skunny11
        Frequent Visitor
        6 years ago

        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.