Text Based on The Selected Count

[I_NeedMorePower]

Hello power people!

I have a list of items, and I want to a create a card visual that will display based on how much is selected (highlighted) on a table.

For Example:

1- IF the user highlighted a single item on a table visual, the card will display the item ID.

2- And IF the user highlighted multiple items, the card will display the number of highlighted items (3 Items, 4 Items, 20 items etc..).

3- And IF the user has not highlighted anything, which means all the items are selected,  I want the card visual to display "ALL" word.

I already did most of the work I believe, but my issue is I don't know how to make a condition that will check if all the items are selected (my 3rd condition above).

Here is my DAX code:

Measure = 
VAR __DISTINCT_VALUES_COUNT = DISTINCTCOUNT('Sales Lines (Merge)'[ItemId])
VAR __MAX_VALUES_TO_SHOW = 1
RETURN
	IF(
		__DISTINCT_VALUES_COUNT > 1000;
		"ALL";
		IF(__DISTINCT_VALUES_COUNT > 1;
            CONCATENATE(__DISTINCT_VALUES_COUNT ; " Items");
            VALUES('Sales Lines (Merge)'[ItemId])
	))

This code will display the ITEM ID if the user highlighted a single item, and if the user selected multiple items it will display the number of highlighted items.

But if you can see my first IF condition: IF( __DISTINCT_VALUES_COUNT > 1000; "ALL";....) this will display "ALL" IF the highlighted items are more than 1000.....

My total Items are 1500 and it can be increased by time.

I don't know how to alter my IF condition to check if ALL items were selected (highlighted).

How should I alter the IF condition? or maybe there are some kind of constant variables I can use?

Thanks in advance!

[OwenAuger · Accepted solution]

Hello @I_NeedMorePower 

You can use the ISFILTERED function to test whether the ItemId column has not been filtered (by highlighting rows of the table), assuming the ItemId column is in the table.

Otherwise you could try ISCROSSFILTERED.

Something like:

Measure =
VAR __DISTINCT_VALUES_COUNT =
    DISTINCTCOUNT ( 'Sales Lines (Merge)'[ItemId] )
VAR __MAX_VALUES_TO_SHOW = 1
RETURN
    IF (
        NOT ISFILTERED ( 'Sales Lines (Merge)'[ItemId] );
        "ALL";
        IF (
            __DISTINCT_VALUES_COUNT > __MAX_VALUES_TO_SHOW;
            CONCATENATE ( __DISTINCT_VALUES_COUNT; " Items" );
            VALUES ( 'Sales Lines (Merge)'[ItemId] )
        )
    )

Does that work in your case?

[MFelix · Accepted solution]

Hi @I_NeedMorePower,

Try to use this formula to replace your 1000 in the formula.

DISTINCT_VALUES_COUNT = CALCULATE( DISTINCTCOUNT('Sales Lines (Merge)'[ItemId]);ALL('Sales Lines (Merge)'[ItemId]))

And instead of doing it > make =

Regard
MFelix

[I_NeedMorePower]

Oh this works too!

Intresting, using calculate with ALL expression...

Thank you that was very informative!

[OwenAuger · Accepted solution]

Hello @I_NeedMorePower 

You can use the ISFILTERED function to test whether the ItemId column has not been filtered (by highlighting rows of the table), assuming the ItemId column is in the table.

Otherwise you could try ISCROSSFILTERED.

Something like:

Measure =
VAR __DISTINCT_VALUES_COUNT =
    DISTINCTCOUNT ( 'Sales Lines (Merge)'[ItemId] )
VAR __MAX_VALUES_TO_SHOW = 1
RETURN
    IF (
        NOT ISFILTERED ( 'Sales Lines (Merge)'[ItemId] );
        "ALL";
        IF (
            __DISTINCT_VALUES_COUNT > __MAX_VALUES_TO_SHOW;
            CONCATENATE ( __DISTINCT_VALUES_COUNT; " Items" );
            VALUES ( 'Sales Lines (Merge)'[ItemId] )
        )
    )

Does that work in your case?

[I_NeedMorePower]

Yes it does! Thank you for your help! I still lack the knowneldge of DAX functions, but hopefully with time I will get used to it 🙂

Thanks again!