20. Dynamic Text and Dynamic Colour (Conditional Formatting)
20.3 Showing Several Selected Cities (CONCATENATEX)
Selected Cities =
VAR SelCount = COUNTROWS(VALUES(DarkStore[City]))
VAR TotalCount = COUNTROWS(ALL(DarkStore[City]))
RETURN
SWITCH(TRUE(),
NOT ISFILTERED(DarkStore[City]) || SelCount = TotalCount, "All cities",
SelCount <= 3, CONCATENATEX(VALUES(DarkStore[City]), DarkStore[City], ", ", DarkStore[City], ASC),
SelCount & " cities selected")
Title Delivery = "Avg delivery time – " & [Selected Cities]
Mitrano, with Pune and Kolhapur selected, the title reads "Avg delivery time – Kolhapur, Pune". With 5 cities selected, it reads "… – 5 cities selected".
Ravindra Bagale's Tip
He bagha, mitrano: using VALUES in a title without handling multiple values gives "A table of multiple values was supplied where a single value was expected". Use SELECTEDVALUE or CONCATENATEX. Lakshat theva, ISFILTERED returns TRUE only for direct filters on that column, so use ISCROSSFILTERED when a filter on another column should count. Practice kara, mag ekdum sope vatel.