DAX to calculate max count of rows and return certain text

[cathoms]

I hope the subject line makes sense. I'm very new to DAX. I have a filter for Discharge Unit and I want to show the selected discharge units at the top of my page. That was set up by another person and looks like this:

There are about three dozen units so currently we have the report set up to max out the displayed units at 10. So, if a user selects from one to ten units all of them will be displayed. If they select more than 10 the top 10 will be displayed along with "etc."., which looks like this:

What I want is to add to the measure so that when all of the units are selected, it says "Discharge Department(s): All" at the top. I'm not sure how to add in the syntax to return "All" when that maximum is reached. Here is the current DAX:

SelectedDischargeDepartment = "Discharge Department(s): " &
VAR __DISTINCT_VALUES_COUNT = DISTINCTCOUNT('PI Hospital Template - 7199'[DischargeUnit])
VAR __MAX_VALUES_TO_SHOW = 10
RETURN
	IF(
		__DISTINCT_VALUES_COUNT > __MAX_VALUES_TO_SHOW,
		CONCATENATE(
			CONCATENATEX(
				TOPN(
					__MAX_VALUES_TO_SHOW,
					VALUES('PI Hospital Template - 7199'[DischargeUnit]),
					'PI Hospital Template - 7199'[DischargeUnit],
					ASC
				),
				'PI Hospital Template - 7199'[DischargeUnit],
				"; ",
				'PI Hospital Template - 7199'[DischargeUnit],
				ASC
			),
			", etc."
		),
		CONCATENATEX(
			VALUES('PI Hospital Template - 7199'[DischargeUnit]),
			'PI Hospital Template - 7199'[DischargeUnit],
			"; ",
			'PI Hospital Template - 7199'[DischargeUnit],
			ASC
		)
	)

I think the following should work to get a max count of the DischargeUnit: 

VAR __MAX_COUNT = MAXX(DISTINCTCOUNT('PI Hospital Template - 7199'[DischargeUnit])

but I'm not sure where to go from here to return "All" if DISTINCT_VALUES_COUNT = MAX_COUNT.

Any help would be much appreciated!

[v-easonf-msft]

Hi, @cathoms 

VAR __DISTINCT_VALUES_COUNT =
        DISTINCTCOUNT ( 'PI Hospital Template - 7199'[DischargeUnit] )

 If  you are using a card visual to display your measure , you may not get the right value of  this variable "__DISTINCT_VALUES_COUNT" in you measure.

You need to apply  "distinctcount" to   a  summarized table  to  get the  value of  variable  __DISTINCT_VALUES_COUNT.

You can check  @amitchanda  solution in this thread.

https://community.powerbi.com/t5/Desktop/Card-Visual-Measure-with-Distinct-Count/m-p/1405570

If there is something I didn’t explain clearly, please share the sample file for testing.

Best Regards,
Community Support Team _ Eason

[AlB · Accepted solution]

@cathoms 

ALL( ) with a column returns all the unique values in a column. That should be fine. There must be something in the model that I do not see.  

The slicer is  with 'PI Hospital Template - 7199'[DischargeUnit], correct?

Try this. Create this debug measure and palce it in a card visual.

Measure1 = 
COUNTROWS ( ALL ( 'PI Hospital Template - 7199'[DischargeUnit] ) )

 Is the result the number of  DischargeUnits ?

Then the same for

Measure2 = 
DISTINCTCOUNT( 'PI Hospital Template - 7199'[DischargeUnit] )

Is the result the number of items selected in the slicer?

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[cathoms]

Yes to both questions re: the Debug measures. When I set up a page containing the cards with the measures as above, and add the Discharge Department(s): label/measure it works as it should. After I publish and refresh It doesn't work on the page I want it on, however. I wonder if the measure is affected by other filters on the page. I think I need to reach out to the report builder who set this up in the first place.

[AlB]

@cathoms 

With your change the definitions for __DISTINCT_VALUES_COUNT and  __MAX_COUNT are exactly the same so, of course,  _DISTINCT_VALUES_COUNT =  __MAX_COUNT always and the code returns "All"

My version should work. What is it that doesn't exactly?

Can you share the pbix so that I can have a look?

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[cathoms]

Sorry, I can't share because of private health information. As to what isn't working, it still shows ten discharge units and "etc." when all are selected or when there is no selection in the discharge unit slicer/filter, rather than "All". Basically, there is no change in behavior. 

My concern with your version of the variable is that I think it is counting all ~ 130,000 rows in the table rather than just counting the distinct number of Discharge Units. As I said, flat file, not a star schema so the reference table is the entire dataset. Not my choice!

I appreciate you working on this.

[AlB]

Hi @cathoms 

Try this. Take into account that it will treat the case of no selection in the slicer in the same ways as if all items are selected. That is the ususal behaviour. A different behaviour can be implemented using ISFILTERED( )

SelectedDischargeDepartment =
"Discharge Department(s): "
    &
    VAR __DISTINCT_VALUES_COUNT =
        DISTINCTCOUNT ( 'PI Hospital Template - 7199'[DischargeUnit] )
    VAR __MAX_COUNT =
        COUNTROWS ( ALL ( 'PI Hospital Template - 7199'[DischargeUnit] ) )
    VAR __MAX_VALUES_TO_SHOW = 10
    RETURN
        IF (
            __DISTINCT_VALUES_COUNT = __MAX_COUNT,
            "All",
            IF (
                __DISTINCT_VALUES_COUNT > __MAX_VALUES_TO_SHOW,
                CONCATENATE (
                    CONCATENATEX (
                        TOPN (
                            __MAX_VALUES_TO_SHOW,
                            VALUES ( 'PI Hospital Template - 7199'[DischargeUnit] ),
                            'PI Hospital Template - 7199'[DischargeUnit], ASC
                        ),
                        'PI Hospital Template - 7199'[DischargeUnit],
                        "; ",
                        'PI Hospital Template - 7199'[DischargeUnit], ASC
                    ),
                    ", etc."
                ),
                CONCATENATEX (
                    VALUES ( 'PI Hospital Template - 7199'[DischargeUnit] ),
                    'PI Hospital Template - 7199'[DischargeUnit],
                    "; ",
                    'PI Hospital Template - 7199'[DischargeUnit], ASC
                )
            )
        )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[cathoms]

Ah, yes! Nested IF statements... of course! Funny how things become obvious in hindsight. Still not working, however.

AIB's MAX_COUNT variable did not do the trick, 

    VAR __MAX_COUNT =
        COUNTROWS ( ALL ( 'PI Hospital Template - 7199'[DischargeUnit] ) )

so I replaced it with 

    VAR __MAX_COUNT =
        DISTINCTCOUNT ( 'PI Hospital Template - 7199'[DischargeUnit] )

This is probably because the table is the dataset rather than a lookup table. We are using a huge flat file instead of setting up a star schema for this report... Don't get me started. Not my decision! 😖

Anywho, the whole thing looks like this:

SelectedDischargeDepartment =
"Discharge Department(s): "
    &
    VAR __DISTINCT_VALUES_COUNT =
        DISTINCTCOUNT ( 'PI Hospital Template - 7199'[DischargeUnit] )
    VAR __MAX_COUNT =
        DISTINCTCOUNT ( 'PI Hospital Template - 7199'[DischargeUnit] )
    VAR __MAX_VALUES_TO_SHOW = 10
    RETURN
        IF (
            __DISTINCT_VALUES_COUNT = __MAX_COUNT,
            "All",
            IF (
                __DISTINCT_VALUES_COUNT > __MAX_VALUES_TO_SHOW,
                CONCATENATE (
                    CONCATENATEX (
                        TOPN (
                            __MAX_VALUES_TO_SHOW,
                            VALUES ( 'PI Hospital Template - 7199'[DischargeUnit] ),
                            'PI Hospital Template - 7199'[DischargeUnit], ASC
                        ),
                        'PI Hospital Template - 7199'[DischargeUnit],
                        "; ",
                        'PI Hospital Template - 7199'[DischargeUnit], ASC
                    ),
                    ", etc."
                ),
                CONCATENATEX (
                    VALUES ( 'PI Hospital Template - 7199'[DischargeUnit] ),
                    'PI Hospital Template - 7199'[DischargeUnit],
                    "; ",
                    'PI Hospital Template - 7199'[DischargeUnit], ASC
                )
            )
        )

Unfortunately, now it says "Discharge Department(s): All" regardless of which or how many discharge units are selected in the filter...