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

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

    • Skunny11's avatar
      Skunny11
      Frequent Visitor
      6 years ago

      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's avatar
        v-lid-msft
        Community Support
        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.