DAX Measure: Visual Title that Changes based on Slicer Suggestion, Returning Selection or StaticText

[JHScout23]

Hello!

I am currently working on creating a report for monthly project data. I have all of the data working the way I want, but I am running into some issues when it comes to a dynamic visual title that I am trying to create. I was able to get one portion working, which is the Project that is being viewed. When I try to do the same with the Month, it displays only the static text created rather than any selection. Below is the DAX that is working, which is the Project.

Multi: Selected Project = 
VAR __DISTINCT_VALUES_COUNT = DISTINCTCOUNT('KS_Fees_Clockify_Hours'[Project])
VAR __MAX_VALUES_TO_SHOW = 3
VAR StaticText = "for All Projects "
RETURN
IF(ISFILTERED(KS_Fees_Clockify_Hours[Project]),
	IF(
		__DISTINCT_VALUES_COUNT > __MAX_VALUES_TO_SHOW,
		CONCATENATE(
            StaticText&
			CONCATENATEX(
				TOPN(
					__MAX_VALUES_TO_SHOW,
					VALUES('KS_Fees_Clockify_Hours'[Project]),
					'KS_Fees_Clockify_Hours'[Project],
					ASC
				),
				'KS_Fees_Clockify_Hours'[Project],
				", ",
				'KS_Fees_Clockify_Hours'[Project],
				ASC
			),
			", etc."
		),
		CONCATENATEX(
			VALUES('KS_Fees_Clockify_Hours'[Project]),
			'KS_Fees_Clockify_Hours'[Project],
			", ",
			'KS_Fees_Clockify_Hours'[Project])
	), StaticText)

This returns 3 projects, and anything after returns an "etc.". If nothing is selected, then it displays text that states, "for All Projects".

Now I try the same thing for Months, which are displayed a the actual Month name, so they are formatted as text. Below is the DAX for that.

Multi: Selected Month = 
VAR DistinctValues = DISTINCTCOUNT(KS_Fees_Clockify_Hours[Month])
VAR MaxValues = 3
VAR StaticText = "for a 12-Month Period"
RETURN
IF(ISFILTERED(KS_Fees_Clockify_Hours[Month]),
IF(
    DistinctValues > MaxValues,
    CONCATENATE(
        StaticText&
        CONCATENATEX(
            TOPN(MaxValues,
            VALUES(KS_Fees_Clockify_Hours[Month]),
            KS_Fees_Clockify_Hours[Month],
            ASC
            ),
            KS_Fees_Clockify_Hours[Month],
            ", ",
            KS_Fees_Clockify_Hours[Month],
            ASC
        ),
        ", etc."
    ),
    CONCATENATEX(
        VALUES(KS_Fees_Clockify_Hours[Month]),
        KS_Fees_Clockify_Hours[Month],
        ", ",
        KS_Fees_Clockify_Hours[Month])
        ), StaticText)

When I run this measure, it always comes back with my static text of "for a 12-Month Period" no matter what the selection is. I have tried alternate versions, such as taking away the static text but then it returns a blank result. I take away one of the "StaticText" along with the IF(ISFILTERED( portion of my code, and it returns three months with the etc afterwards, which would be great but it is when there is no selection.

I am at a loss to exactly what I am doing wrong, but I feel like it is just something I have overlooked. I would post my data but unfortunately everything is company-owned, so if I scrub it then there will be no data. The months are January, February, March, April, May, June, July, August, September, October, November, December. If you want to test out the Project DAX, just use number 1-10, as Proj1, Proj2, etc.

Hopefully I have been specific enough as to my issue, but if there is anything else that I can provide, such as a better explanation, please let me know and I will see what I am able to do.

Thank you!

[JHScout23]

Hello!

Sorry for the late response. I have been putting out some fires that took up most of my time. 

I tried the change in the DAX code that you provided and it is giving me the ability to show multiple month selections, but when I have nothing selected, it displays every month with the "for a 12-Month Period". 

I really thank you for all the help you've given me, and I am going to keep chipping away at this until I get it to work!

Thanks!

[123abc]

It seems like the issue lies within your DAX measure for handling the selected months. Based on your description, it seems you want a dynamic title that displays either the selected months or the static text "for a 12-Month Period" if no specific months are selected. Let's refine your DAX measure:

Multi: Selected Month =
VAR DistinctValues = DISTINCTCOUNT(KS_Fees_Clockify_Hours[Month])
VAR MaxValues = 3
VAR StaticText = "for a 12-Month Period"
RETURN
IF(
ISFILTERED(KS_Fees_Clockify_Hours[Month]),
IF(
DistinctValues > MaxValues,
CONCATENATE(
"for ",
CONCATENATEX(
TOPN(
MaxValues,
VALUES(KS_Fees_Clockify_Hours[Month]),
KS_Fees_Clockify_Hours[Month],
ASC
),
KS_Fees_Clockify_Hours[Month],
", ",
KS_Fees_Clockify_Hours[Month],
ASC
),
", etc."
),
CONCATENATEX(
VALUES(KS_Fees_Clockify_Hours[Month]),
KS_Fees_Clockify_Hours[Month],
", "
)
),
StaticText
)

Here's what I've changed:

1. Removed the unnecessary StaticText declaration inside the IF statement.
2. Refactored the CONCATENATEX and CONCATENATE functions to directly concatenate strings.
3. Used the ISFILTERED function to check if a month is selected.
4. If months are selected, it checks if the count of distinct months is greater than the maximum allowed. If yes, it concatenates the selected months using CONCATENATEX and TOPN functions, otherwise, it simply concatenates the selected months.
5. If no months are selected (i.e., the filter context is empty), it returns the static text "for a 12-Month Period".

Please replace the measure in your Power BI report with the updated one and see if it works as expected. If there are still issues, let me know, and I'll assist you further!

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[JHScout23]

Hello 123abc!

The change in the measure worked for the most part! The only issue is that the in the last portion of the DAX, the StaticText is showing as an unexpected parameter. The code is displaying that the two parenthesese after the last ", " are causing the measure to be outside of the first IF( statement.

Any thoughts? The rest of the measure works perfectly, displaying the multi-selected months (even in proper format showing the months concurrently, which is awesome so thank you again!) or even just a single selected month, just the StaticText is having an issue. If I take out the StaticText, the measure successfully computes just with all the months showing.

Let me know what you think!

Thank you so much again! One step closer! I appreciate you and your tips!

[123abc]

I see the issue with the StaticText parameter causing problems with the parenthesis. Let's adjust the measure to ensure it works properly:

Multi: Selected Month =
VAR DistinctValues = DISTINCTCOUNT(KS_Fees_Clockify_Hours[Month])
VAR MaxValues = 3
RETURN
IF(
ISFILTERED(KS_Fees_Clockify_Hours[Month]),
IF(
DistinctValues > MaxValues,
CONCATENATE(
"for ",
CONCATENATEX(
TOPN(
MaxValues,
VALUES(KS_Fees_Clockify_Hours[Month]),
KS_Fees_Clockify_Hours[Month],
ASC
),
KS_Fees_Clockify_Hours[Month],
", ",
KS_Fees_Clockify_Hours[Month],
ASC
),
", etc."
),
CONCATENATEX(
VALUES(KS_Fees_Clockify_Hours[Month]),
KS_Fees_Clockify_Hours[Month],
", ",
KS_Fees_Clockify_Hours[Month]
)
),
"for a 12-Month Period"
)

In this adjustment, I removed the separate StaticText variable declaration and directly incorporated it within the CONCATENATE function.

This should resolve the issue you were facing with the StaticText parameter causing problems with the parenthesis. Now the measure should properly display the selecte

d months or the static text "for a 12-Month Period" based on the slicer selection. Let me know if you encounter any further issues!

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.