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.

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_
)   

       

      • AlB's avatar
        AlB
        Icon for Community Champion rankCommunity Champion
        6 years ago
        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  Datanaut