Complex dynamic text

[Marshmallow]

Hi, I need help with creating dax for dynamic title. The outcome I would like to display is: "**When a selected region is selected in the filter, then the text will display --> [Selected Region] has [count breach] breach recorded. The type of breach recorded is [type of breach]  and the breach [if yes then showed "has been"] else shown as "has not been" rectified.

** When no Region is selected -- show text as 'Please select Region to display the compliance information'

The data table is below as example:

Region	Organisation	Type of Breach	Breach Rectified
Adelaide	A	Volcano	Yes
Brisbane	B	Thunderstorm	Yes
Cairns	B	Volcano	Yes
Adelaide	C	Thunderstorm	No
Sydney	D	Seastorm	No

Hopefully this makes sense and can be done.

[Ritaf1983]

Hi @Marshmallow 

The logic of needed dax is similar to :

title =
if(SELECTEDVALUE('Table'[Region]) = blank(),"Please select Region to display the compliance information",
if(MAX('Table'[Breach Rectified])= "Yes",SELECTEDVALUE('Table'[Region]) & " has "& CALCULATE(DISTINCTCOUNT('Table'[Type of Breach]),ALLEXCEPT('Table','Table'[Region]))&" breach recorded. The type of breach is : " & CONCATENATEX(values('Table'[Type of Breach]),'Table'[Type of Breach],",")&"has been rectified",SELECTEDVALUE('Table'[Region]) & " has "& CALCULATE(DISTINCTCOUNT('Table'[Type of Breach]),ALLEXCEPT('Table','Table'[Region]))&" breach recorded. The type of breach is : " & CONCATENATEX(values('Table'[Type of Breach]),'Table'[Type of Breach],",")&"has been  not rectified"))

Here’s the translation:

But there might be some misunderstandings with the calculations inside that you didn’t consider.

You're filtering only by region, and it’s possible that the Rectified field could have both "Yes" and "No" values. It’s also not clear how to handle counting of the branches, etc.

In any case, from the base I’ve built, you’ll be able to get what you need once you adjust the calculation logic.

The pbix is attached

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

[Kedar_Pande · Accepted solution]

@Marshmallow 

Create a new measure:

DynamicTitle =
VAR SelectedRegion = SELECTEDVALUE('YourTable'[Region])
VAR BreachCount = CALCULATE(COUNTROWS('YourTable'), 'YourTable'[Region] = SelectedRegion)
VAR BreachType = SELECTEDVALUE('YourTable'[Type of Breach])
VAR BreachRectified = SELECTEDVALUE('YourTable'[Breach Rectified])

RETURN 
IF(
ISBLANK(SelectedRegion),
"Please select a Region to display the compliance information",
SelectedRegion & " has " & BreachCount & " breach recorded. The type of breach recorded is " & BreachType & " and the breach " &
IF(BreachRectified = "Yes", "has been", "has not been") & " rectified."
)

Use this measure in a card visual to display the dynamic title.

💌If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[Marshmallow]

Hi Kedar, it is almost there. The Breach Type won't be a filter selection so we can't use selectedvalue. what's the best way to say

_var BreachType = if([BreachRectified]="Yes", "Yes", "No")

so in the return it will find the column 'BreachRectified", if it has yes, then the breach is rectified, else no.

[TomMartens]

Hey @Marshmallow ,

please provide the message to be shown if Adelaide is selected. There are two Types of Breaches, one is rectified one is not.
Also, it's not clear how the message should look like if there are more rectified/not rectified breaches for the same type of breach or different types of breaches.

Regards,
Tom