Forum Discussion
Complex dynamic text
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.
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
4 Replies
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,
TomCreate 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 LinkedInHi 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.
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
