Need Help With Dynamic Insight Text Box

[RichOB]

I've seen a few videos online about how to add dynamic insights into text boxes but I can't seem to get the measures to work properly with my data.  My real-life report will have year filters, so if the Location and Counts below could change with the year filter that would be great.

If I want 2 text boxes to say:

" ....(Location)...has the most number of incidents with...(Incident Count)"

"...(Location)...has had the police called...(Police Called Count)...times"

Location	Incident Number	Police Called
Manchester	Inc1	1
Manchester	Inc2	0
Manchester	Inc2	0
London	Inc4	1
London	Inc5	1
London	Inc6	1
London	Inc7	1
London	Inc8	1
Newcastle	Inc9	1
Newcastle	Inc10	1
Newcastle	Inc11	0

Thanks!

[Anonymous · Accepted solution]

Thanks for the reply from saud968  and Selva-Salimi , please allow me to provide another insight:

Hi, @RichOB 

Regarding the issue you raised, my solution is as follows:

1.I have created the following table and the column names and data are the data you have given:

2.Firstly, you need to create the following calculated table and use it as a slicer, ensuring that the slicer does not directly affect the original data.

select1 = VALUES('Table'[Location])

3. Below are the measure I've created for your needs:

Dynamic Text = 
VAR Location =
    MAX ( 'select1'[Location] )
VAR IncidentCount =
    COUNTROWS (
        FILTER ( 'Table', 'Table'[Location] = SELECTEDVALUE ( 'select1'[Location] ) )
    )
VAR PoliceCount =
    SUM ( 'Table'[Police Called] )
RETURN
    IF (
        ISFILTERED ( 'select1'[Location] ),
        Location & " has the most number of incidents with " & IncidentCount
            & UNICHAR ( 10 ) & Location & " has had the police called " & PoliceCount & " times.",
        BLANK ()
    )

4.Here's my final result, which I hope meets your requirements.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

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

[saud968]

Measure to find the location with the most incidents:
LocationWithMostIncidents =
CALCULATE(
FIRSTNONBLANK('Table'[Location], 1),
TOPN(
1,
SUMMARIZE(
'Table',
'Table'[Location],
"IncidentCount", COUNT('Table'[Incident Number])
),
[IncidentCount], DESC
)
)

Measure to count incidents for that location:
IncidentCount =
CALCULATE(
COUNT('Table'[Incident Number]),
'Table'[Location] = [LocationWithMostIncidents]
)

Single measure to create the dynamic text:
IncidentText =
[LocationWithMostIncidents] & " has the most number of incidents with " & [IncidentCount]

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!

[Selva-Salimi]

Hi @RichOB 

you can easily write a measure like following and add it to a "card" visual instead:

Measure = SELECTEDVALUE('Table'[Location]) & " has the most number of incidents with " & COUNT('Table'[incident])

and the same for the other one!

If this post helps, then I would appreciate a thumbs up 👍 and mark it as the solution to help the other members find it more quickly.