Need help showing trends

Hi RichOB ,

First of all, use this DAX to create a calculated column:

 

Q_Number = VALUE(RIGHT('Table'[Q], 1))

 

Question1:

 

In Quarter 1, Manchester had the most Assault issues by people who lived alone between the ages of 25-30.

 

Use this DAX to create a mesure:

 

Q1_Assault = 
CALCULATE(
    COUNTROWS('Table'),
    'Table'[Issue] = "Assault"
)

 

Then create the column chart:

Question2:

 

In Quarter 2, the Assault issues decreased by 75%.

 

Use this DAX to create a measure:

 

Assault_difference = 
VAR _current = 
CALCULATE(
    COUNTROWS('Table'),
    ALL('Table'),
    'Table'[Q_Number] = MAX('Table'[Q_Number]) && 'Table'[Issue] = "Assault"
)
VAR _previous = 
CALCULATE(
    COUNTROWS('Table'),
    ALL('Table'),
    'Table'[Q_Number] = MAX('Table'[Q_Number]) - 1 && 'Table'[Issue] = "Assault"
)
RETURN
IF(
    _previous <> BLANK(),
    (_current - _previous) / _previous,
    0
)

 

Then create the line chart:

Question3:

 

100% of Edinburgh's issues were drug-related by people who did not live alone between the ages of 40-45.

 

Use this DAX to create a measure:

 

Issue_Edinburgh = 
VAR _Edinburgh = 
CALCULATE(
    COUNTROWS('Table'),
    'Table'[Location] = "Edinburgh" && 'Table'[Q] = MAX('Table'[Q])
)
VAR _currentissue = 
CALCULATE(
    COUNTROWS('Table'),
    'Table'[Location] = "Edinburgh" && 'Table'[Q] = MAX('Table'[Q]) && 'Table'[Issue] = MAX('Table'[Issue])
)
RETURN
_currentissue / _Edinburgh

 

Then create the column chart:

Question4:

 

In Quarter 3, Edinburgh improved their Drug issue by 50%.

 

Use this DAX to create a measure:

 

Drug_Edinburgh_difference = 
VAR _current = 
CALCULATE(
    COUNTROWS('Table'),
    ALL('Table'),
    'Table'[Q_Number] = MAX('Table'[Q_Number]) && 'Table'[Issue] = "Drug" && 'Table'[Location] = "Edinburgh"
)
VAR _previous = 
CALCULATE(
    COUNTROWS('Table'),
    ALL('Table'),
    'Table'[Q_Number] = MAX('Table'[Q_Number]) - 1 && 'Table'[Issue] = "Drug" && 'Table'[Location] = "Edinburgh"
)
RETURN
IF(
    _previous <> BLANK(),
    (_current - _previous) / _previous,
    0
)

 

Then create the line chart:

 

Question5:

 

Manchester had the most Drug issues being 100% by people where we were unsure if they lived alone, aged between 40-45.

 

Use this DAX to create a measure:

 

Drug = 
CALCULATE(
    COUNTROWS('Table'),
    'Table'[Issue] = "Drug"
)

 

Then create the column chart:

Question6:

 

In Quarter 4,  Manchester had a reduction of 50% in drug-related issues.

 

Use this DAX to create a measure:

 

Drug_Manchester_difference = 
VAR _current = 
CALCULATE(
    COUNTROWS('Table'),
    ALL('Table'),
    'Table'[Q_Number] = MAX('Table'[Q_Number]) && 'Table'[Issue] = "Drug" && 'Table'[Location] = "Manchester"
)
VAR _previous = 
CALCULATE(
    COUNTROWS('Table'),
    ALL('Table'),
    'Table'[Q_Number] = MAX('Table'[Q_Number]) - 1 && 'Table'[Issue] = "Drug" && 'Table'[Location] = "Manchester"
)
RETURN
IF(
    _previous <> BLANK(),
    (_current - _previous) / _previous,
    0
)

 

Then create the line chart:

Question7:

 

Edinburgh had an Increase in Missing Persons by 50% by people who did not live alone, people ages 50-55.

 

Use this DAX to create a measure:

 

Missing Person_Edinburgh_difference = 
VAR _current = 
CALCULATE(
    COUNTROWS('Table'),
    ALL('Table'),
    'Table'[Q_Number] = MAX('Table'[Q_Number]) && 'Table'[Issue] = "Missing Person" && 'Table'[Location] = "Edinburgh"
)
VAR _previous = 
CALCULATE(
    COUNTROWS('Table'),
    ALL('Table'),
    'Table'[Q_Number] = MAX('Table'[Q_Number]) - 1 && 'Table'[Issue] = "Missing Person" && 'Table'[Location] = "Edinburgh"
)
RETURN
IF(
    _previous <> BLANK(),
    (_current - _previous) / _previous,
    0
)

 

Then create the line chart:

But IMHO, using measures to show certain trends is not a good choice. And according to your description, there are many trends you need to know. Using DAX to create measures and calculated columns will occupy a lot of your memory. It will be more convenient and direct to use Q&A or the Copilot function that comes with Power BI. Please refer to the following documents:
Create a Q&A visual in a report - Power BI | Microsoft Learn
Explore and create visuals in your reports using Power BI Q&A - Power BI | Microsoft Learn
Learn how to use natural language to explore data with Power BI Q&A - Power BI | Microsoft Learn

Overview of Copilot for Power BI - Power BI | Microsoft Learn
Enable Fabric Copilot for Power BI - Power BI | Microsoft Learn
Create reports in Power BI Desktop with Copilot - Power BI | Microsoft Learn
Add Copilot summaries to email subscriptions - Power BI | Microsoft Learn

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