Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
@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"
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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.
Hi, @Anonymous
If it is convenient, can you share some saample data for further research?
Best Regards,
Community Support Team _ Eason
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |