Dynamic text based on filter selection

[Read39487]

Hi all,

I'm looking for a method to display a text dynamically based on filter selection.

I have checked different methods suggested in other articles but none works as expected so far.

The table I have has 2 columns like below:

A	Group 1
B	Group 1
C	Group 1
D	Group 2
E	Group 3

I would like to create a text that would show for A, B, C as

(1) "Group 1" when all options in Group 1 are selected (A,B,C are all selected)

(2) A when in Group 1 only A  is selected. B when in Group 1 only B is selected. C when in Group 1 only C is selected. 

(3) A/B when in Group 1 only A and B are selected, etc

It would be as below presented as a table

Product	When A, B, and C are all selected	When only A is selected	When only A and B are selected
A	Group 1	A	A/B
B	Group 1	A	A/B
C	Group 1	A	A/B
D	Group 2	Group 2	Group 2
E	Group 3	Group 3	Group 3

I'm mainly looking for method that would work for 1st and 2nd situation (when A and B and C are all selected or only A is selected).

I have tried a formula shared in another article as below but it shows only blank no matter what options I selected.

VAR _count = CALCULATE(COUNT('Table'[Product]), FILTER('Table', [Product] IN VALUES('Table'[Product])))

RETURN

IF(_count = 1, MAX([Product]), "Multiple")

[rohit1991 · Accepted solution]

Hii @Read39487 

To return dynamic text based on the selected products, you first need to detect how many items from Group 1 are selected. Once you know the count, you can build the text accordingly. A simple pattern is:

SelectedText :=
VAR L = VALUES('Table'[Product])
VAR C = COUNTROWS(L)
RETURN
SWITCH(
    TRUE(),
    C = 3, "Group 1",
    C = 1, MAX('Table'[Product]),
    C = 2, CONCATENATEX(L, [Product], "/"),
    "Multiple"
)

This returns “Group 1” when A,B,C are all selected, returns A, B, or C when only one is selected, and returns A/B, A/C, etc. when two are selected. This approach reliably handles all three scenarios you described.

[MFelix · Accepted solution]

Hi @Read39487 ,

Try the following code:

Dynamic Text =	SWITCH(
		TRUE(),
		COUNTROWS('Table') = COUNTROWS(FILTER(
			ALL('Table'),
			'Table'[Group] = MAX('Table'[Group])
		)), SELECTEDVALUE('Table'[Group]),
		COUNTROWS(SUMMARIZE(
			'Table',
			'Table'[Group]
		)) = 1 &&
		COUNTROWS('Table') <= COUNTROWS(FILTER(
			ALL('Table'),
			'Table'[Group] = MAX('Table'[Group])
		)), CONCATENATEX(
			'Table',
			'Table'[Product],
			"/"
		),
		COUNTROWS('Table') = 1, SELECTEDVALUE('Table'[Product]),
		"Multiple Values"
	)

[danextian · Accepted solution]

Hi @Read39487 

Try this:

text = 
-- Get the currently selected product (if only one is selected)
VAR _prodSelected = SELECTEDVALUE(Tbl[Product])

-- Get the currently selected group (if only one is selected)
VAR _group = SELECTEDVALUE(Tbl[Group])

-- Get all products in the current filter context
VAR _productsSelected = VALUES(Tbl[Product])

-- Get all products that belong to Group 1, ignoring current filters
VAR _group1Products = 
    CALCULATETABLE(
        VALUES(Tbl[Product]), 
        FILTER(ALL(Tbl), Tbl[Group] = "Group 1")
    )

-- Check if all selected products are exactly Group 1 products
VAR _allGroup1Selected = 
    VAR _selectedVsGroup = COUNTROWS(EXCEPT(_group1Products, _productsSelected))   -- Items in Group 1 not selected
    VAR _groupVsSelected = COUNTROWS(EXCEPT(_productsSelected, _group1Products))   -- Items selected not in Group 1
    RETURN ISBLANK(_selectedVsGroup) && ISBLANK(_groupVsSelected)                  -- True if selection matches all of Group 1 exactly

-- Precompute concatenated product names for multiple selections
VAR twoProducts = CONCATENATEX(_productsSelected, [Product], "/")

-- Check if a subset of Group 1 products is selected
VAR _subsetGroup1Selected = 
    COUNTROWS(INTERSECT(_productsSelected, _group1Products)) = COUNTROWS(_productsSelected) &&
    COUNTROWS(_productsSelected) < COUNTROWS(_group1Products)

-- Determine the final result based on selection logic
VAR _result  = 
SWITCH(
    TRUE(),
    -- If a single product D or E is selected, return its group
    _prodSelected IN {"D", "E"}, _group,

    -- If all Group 1 products are selected and no other products, return "Group 1"
    _allGroup1Selected, "Group 1",

    -- If a subset of Group 1 products is selected, return the concatenated product names
    _subsetGroup1Selected, twoProducts,

    -- If any other single product is selected, return that product
    NOT _prodSelected IN {"D", "E"}, _prodSelected
)

-- Return the result
RETURN
_result

[Kedar_Pande · Accepted solution]

@Read39487 

Dynamic Selection =
VAR SelectedItems = VALUES('Table'[Product])
VAR ItemCount = COUNTROWS(SelectedItems)
VAR GroupName = MAX('Table'[Group]) // Assumes Group column exists
RETURN
SWITCH(
TRUE(),
ItemCount = 1, SELECTEDVALUE('Table'[Product]), // Single item: "A"
ItemCount = 3 && HASONEVALUE('Table'[Group]), GroupName, // All Group 1: "Group 1"
CONCATENATEX(SelectedItems, 'Table'[Product], "/", "") // Multiple: "A/B"
)

If this answer helped, please click Kudos or Accept as Solution.
-Kedar
LinkedIn: https://www.linkedin.com/in/kedar-pande

[danextian · Accepted solution]

Hi @Read39487 

Try this:

text = 
-- Get the currently selected product (if only one is selected)
VAR _prodSelected = SELECTEDVALUE(Tbl[Product])

-- Get the currently selected group (if only one is selected)
VAR _group = SELECTEDVALUE(Tbl[Group])

-- Get all products in the current filter context
VAR _productsSelected = VALUES(Tbl[Product])

-- Get all products that belong to Group 1, ignoring current filters
VAR _group1Products = 
    CALCULATETABLE(
        VALUES(Tbl[Product]), 
        FILTER(ALL(Tbl), Tbl[Group] = "Group 1")
    )

-- Check if all selected products are exactly Group 1 products
VAR _allGroup1Selected = 
    VAR _selectedVsGroup = COUNTROWS(EXCEPT(_group1Products, _productsSelected))   -- Items in Group 1 not selected
    VAR _groupVsSelected = COUNTROWS(EXCEPT(_productsSelected, _group1Products))   -- Items selected not in Group 1
    RETURN ISBLANK(_selectedVsGroup) && ISBLANK(_groupVsSelected)                  -- True if selection matches all of Group 1 exactly

-- Precompute concatenated product names for multiple selections
VAR twoProducts = CONCATENATEX(_productsSelected, [Product], "/")

-- Check if a subset of Group 1 products is selected
VAR _subsetGroup1Selected = 
    COUNTROWS(INTERSECT(_productsSelected, _group1Products)) = COUNTROWS(_productsSelected) &&
    COUNTROWS(_productsSelected) < COUNTROWS(_group1Products)

-- Determine the final result based on selection logic
VAR _result  = 
SWITCH(
    TRUE(),
    -- If a single product D or E is selected, return its group
    _prodSelected IN {"D", "E"}, _group,

    -- If all Group 1 products are selected and no other products, return "Group 1"
    _allGroup1Selected, "Group 1",

    -- If a subset of Group 1 products is selected, return the concatenated product names
    _subsetGroup1Selected, twoProducts,

    -- If any other single product is selected, return that product
    NOT _prodSelected IN {"D", "E"}, _prodSelected
)

-- Return the result
RETURN
_result

[grazitti_sapna]

Hi @Read39487,

Attached is the pbix with working solution.

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

[MFelix · Accepted solution]

Hi @Read39487 ,

Try the following code:

Dynamic Text =	SWITCH(
		TRUE(),
		COUNTROWS('Table') = COUNTROWS(FILTER(
			ALL('Table'),
			'Table'[Group] = MAX('Table'[Group])
		)), SELECTEDVALUE('Table'[Group]),
		COUNTROWS(SUMMARIZE(
			'Table',
			'Table'[Group]
		)) = 1 &&
		COUNTROWS('Table') <= COUNTROWS(FILTER(
			ALL('Table'),
			'Table'[Group] = MAX('Table'[Group])
		)), CONCATENATEX(
			'Table',
			'Table'[Product],
			"/"
		),
		COUNTROWS('Table') = 1, SELECTEDVALUE('Table'[Product]),
		"Multiple Values"
	)

[MattiaFratello]

See the attached pbix

If this helped please feel free to mark it as a solution and give kudos 👍

[rohit1991 · Accepted solution]

Hii @Read39487 

To return dynamic text based on the selected products, you first need to detect how many items from Group 1 are selected. Once you know the count, you can build the text accordingly. A simple pattern is:

SelectedText :=
VAR L = VALUES('Table'[Product])
VAR C = COUNTROWS(L)
RETURN
SWITCH(
    TRUE(),
    C = 3, "Group 1",
    C = 1, MAX('Table'[Product]),
    C = 2, CONCATENATEX(L, [Product], "/"),
    "Multiple"
)

This returns “Group 1” when A,B,C are all selected, returns A, B, or C when only one is selected, and returns A/B, A/C, etc. when two are selected. This approach reliably handles all three scenarios you described.