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.

      • Skunny11's avatar
        Skunny11
        Frequent Visitor
        6 years ago
        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_
)