Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Current Selections Box Date Hierarchy

Hello,

 

I've been trying to show a filter selction in a "Current Selection" box I made.  I can get it to work with a filter I use for state, but I cannot get it to work for a date hierarchy filter by Year/Month.  As an example of what I'm trying to do, we'll call a field called DateCreated.  This is obviously date data and the hieracrchy function works fine on a the filter.  It is filtering by year and month.  For the current selections box, I'm trying to concatenate a year and month from the DateCreated field.  My Measure is 

DateCreateSelection =
IF (
ISFILTERED ( 'tbl_Data'[DateCreated] ),
"State:" & UNICHAR ( 10 )
& CONCATENATEX ( DISTINCT ( 'tbl_Data'[DateCreated] ), 'tbl_Data'[DateCreated].[Year], ", " ),
"No filter applied"
)

 

I can get it to do either just month or year by adding the .[Year]/.[Month], not both.  Anyone have experience with doing this?  The only way I've gotten it to work is by adding a new column and create a new date column using just month/year from my date created field.  As an example of what I tried is 

 

DateCreateSelection =
IF (
ISFILTERED ( 'tbl_Data'[DateCreated] ),
"State:" & UNICHAR ( 10 )
& CONCATENATEX ( DISTINCT ( 'tbl_Data'[DateCreated] ), 'tbl_Data'[DateCreated].[Year]  & "-" & 'tbl_Data'[DateCreated].[Month], ", " ),
"No filter applied"
)

Which is not working at all.  I should state that I'm very much a DAX noob so I'm still learning the syntax.  

Need Help

3 Replies

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    5 years ago

    Anonymous 

    Can you try the modified measure?

    DateCreateSelection =
IF (
    ISFILTERED ( 'tbl_Data'[DateCreated] ),
    "State:" & UNICHAR ( 10 )
    & CONCATENATEX ( DISTINCT ( 'tbl_Data'[DateCreated] ), Year('tbl_Data'[DateCreated]) & " - " & MONTH('tbl_Data'[DateCreated]), ", " ),
    "No filter applied"
)

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      This partially works, but I get a year and month for every record listed that matches year and month.  I'm looking for a way to only show one month year, so it guess like:

      DateCreateSelection =
      IF (
      ISFILTERED ( 'tbl_Data'[DateCreated] ),
      "State:" & UNICHAR ( 10 )
      & CONCATENATEX ( DISTINCT ( 'tbl_Data'[DateCreated] ), DISTINCT(Year('tbl_Data'[DateCreated]  & " - " & Month('tbl_Data'[DateCreated])), ", " ),
      "No filter applied"
      )

       

      The issue is I cannot DISTINCT the year and month.

      • v-easonf-msft's avatar
        v-easonf-msft
        Icon for Community Support rankCommunity Support
        5 years ago

        Hi, Anonymous 

        If it is convenient, can you share some saample data for further research?

         

        Best Regards,
        Community Support Team _ Eason