Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
best_lina
Frequent Visitor

create a dynamic title based on slicer of date hierarchy capturing all year, quarter, and month

Goal: I have a date slicer of date hierarchy (year, quarter, month). Based on user's selection, I need to create a dynamic title that captures all year, quarter, and month selected. Quarter(s) and month(s) follow after their respective year. For example, 2023 Q1 M1, 2 & 2024 Q2 M4. The title can get long but I will keep the question simple for now and worry about the length later. 

Data Model: One fact table and one calendar table related by the [Date] column. Calendar table was generated from the [Date] column in the fact table. The calendar table has following 4 columns: [Date] - date type,  [Year] & [Quarter] & [Month] - whole number type. 

 

This question is about the date slicer selection. I will save the time by not involving the fact table here. 

 

DAX I need help with: 
Title = 

VAR SelectedYears =
    ADDCOLUMNS(
        VALUES('Calendar'[Year]),
        "SelectedQuarters",
        CONCATENATEX(
            FILTER(
                VALUES('Calendar'[Quarter]),
            'Calendar'[Year] = EARLIER('Calendar'[Year])
            ),
            [Quarter],
            ", "
            ),
        "SelectedMonths",
        CONCATENATEX(
            FILTER(
                VALUES('Calendar'[Month]),
                'Calendar'[Year] = EARLIER('Calendar'[Year])),
                [Month],
                ", ", 'Calendar'[Month], ASC
            )
    )
RETURN
    CONCATENATEX(
        SelectedYears,
        'Calendar'[Year] & " Q" &
         [SelectedQuarters] & " M"
         & [SelectedMonths],
        " & "
    )
 
Problem of this DAX: 
Instead of the desired output (for example: 2023 Q1 M1, 2 & 2024 Q2 M4), the above DAX returns all quarters and months after each year selected: 2023 Q1, 2 M1, 2, 4 & 2024 Q1, 2 M1, 2, 4. 

What is the correct DAX to achieve my goal? Please help. Thank you! 
1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

 

Title = 

VAR SelectedYears =
    SUMMARIZECOLUMNS(
        'Calendar'[Year],
        'Calendar'[Quarter],
        'Calendar'[Month]
)
RETURN
    CONCATENATEX(
        SelectedYears,
        [Year] & " Q" & [Quarter] & " M" & [Month],
        " & "
    )

 

View solution in original post

2 REPLIES 2
best_lina
Frequent Visitor

@lbendlin You are such a genius! Your DAX is simple and works wonder. I could have stopped here. But I added a few lines on top of your DAX to produce the right format for my situation - reduced the title length just a little bit.

 

Title =
VAR SelectedYears =
SELECTCOLUMNS(
    SUMMARIZE(
        'Calendar',
        'Calendar'[Year],
        "Combined_Quarter",
        CONCATENATEX(
            VALUES('Calendar'[Quarter]),
            'Calendar'[Quarter],
            ", ", 'Calendar'[Quarter], ASC
        ),
        "Combined_Month",
        CONCATENATEX(
            VALUES('Calendar'[Month]),
            'Calendar'[Month],
            ", ", 'Calendar'[Month],ASC
        )
    ),
    "Year", [Year],
    "Quarter", [Combined_Quarter],
    "Month", [Combined_Month]
)
Return
CONCATENATEX(
    SelectedYears,
    [Year] & " Q" & [Quarter]
     & " M" & [Month], " & ", [Year], ASC
)
 
My tips/takeaway from this experience is, in my original DAX, the EARLIER function does not work at all in this context. And SUMMARIZECOLUMNS is the right DAX to use.  
 
Thank you @lbendlin
lbendlin
Super User
Super User

 

Title = 

VAR SelectedYears =
    SUMMARIZECOLUMNS(
        'Calendar'[Year],
        'Calendar'[Quarter],
        'Calendar'[Month]
)
RETURN
    CONCATENATEX(
        SelectedYears,
        [Year] & " Q" & [Quarter] & " M" & [Month],
        " & "
    )

 

Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.