Filter Context issue

DAX DAX Calculations
1

I have five tables pulled from different sources that among other fields contain a field for Incident Types. These fields have different names and incident types in each table.

Each table also contains location and date data.

The goal is to report against all Incident Types, Locations and Date.

I’ve used the date table from this site.

I’ve created an “Incident Type” dimension table (DIM_INCIDENT_TYPE) with the code below, and created a One-To-Many relationship between each of the five tables and this DIM table by way of the ‘IncidentTypeKey”.

Each table has a field that contains the number of incidents recorded: ‘Number of Incidents’.

I’ve created measures to calculate the number of incidents for each table. Each is the same as this:

Incidents ASB2 = 
    IF(
        ISBLANK(
            CALCULATE(
                SUMX('Anti-social Behaviour reports', 'Anti-social Behaviour reports'[Number_of_Incidents])
            )
        ), 0,  // If the result is blank, return 0
            CALCULATE(
                SUMX('Anti-social Behaviour reports', 'Anti-social Behaviour reports'[Number_of_Incidents])
            )
    )

I’ve calculated the total Incidents in a measure.

Incidents Total2 = 

    IF(
        ISBLANK(
            CALCULATE(
                [Incidents ASB2] +
                [Incidents CCTV2] +
                [Incidents Guards2] +
                [Incidents POLICE2]+
                [Incidents T12]
                //,KEEPFILTERS(DIM_INCIDENT_TYPE1[Incident Type])
            )
            ), 0,  // If the result is blank, return 0
            CALCULATE(
                [Incidents ASB2] +
                [Incidents CCTV2] +
                [Incidents Guards2] +
                [Incidents POLICE2]+
                [Incidents T12]
                //,KEEPFILTERS(DIM_INCIDENT_TYPE1[Incident Type])
        )
    )

I’m having issues displaying the correct data for Incident Numbers when I filter against ‘Incident Type’ from the DIM table.

When used in a card these display the correct values:
Card Data

I’ve tested using KEEPFILTERS. When I don’t use it there a high number of blank values and when I use it, they are removed but the data is incorrect.

Without KEEPFILTERS
Without KEEPFILTERS

With KEEPFILTERS
With KEEPFILTERS

‘Sum of Incidents DIM’ is the SUM of all incidents from the DIM table which I’ve collected to test the totals but it’s not much use in the DIM table.

This is the data from the DIM table for the Anti-Social Reports so I know there should be a total of 10 incidents. This matches with the source data. While I do have the incident numbers in the DIM table I need to source this from each table because each will contain the date and location data for their respective Incidents.

DIM data
DIM data849×220 37 KB

I feel I’m missing something fundamental but I can’t put my finger on it.
Apologies if the description isn’t clear this is my first post AND I’m new to Power BI and DAX.

Anonymised pbix
Anonymised Data.pbix (697.3 KB)

NOTE The figures in the images are before I anonymised and pared down the data.

This is the DAX to create the DIM table

DIM_INCIDENT_TYPE = 

-- Define a calculated table 'DIM_INCIDENT_TYPE' to summarize incident types and total incidents.
VAR IncidentTypesASB =
    SUMMARIZE('Anti-Social Behaviour Reports',
        'Anti-Social Behaviour Reports'[IncidentType],  -- Get the incident type from ASB table.
        "IncidentTypeKey", "ASB" & "-" & 'Anti-Social Behaviour Reports'[IncidentType],  -- Create a unique incident type key.
        "TableName", "Anti-social Reports",  -- Specify the table name.
        "Incidents", SUM('Anti-Social Behaviour Reports'[Number_of_Incidents])  -- Calculate the total incidents for varification of totals
    )

VAR IncidentTypesCCTV =
    SUMMARIZE('CCTV Requests',
        'CCTV Requests'[ReasonsWhy],  -- Get the incident type from CCTV table.
        "IncidentTypeKey", "CCTV" & "-" & 'CCTV Requests'[ReasonsWhy],  -- Create a unique incident type key.
        "TableName", "CCTV Requests",  -- Specify the table name.
        "Incidents", SUM('CCTV Requests'[Number_of_Incidents])  -- Calculate the total incidents.
    )

VAR IncidentTypesGuards =
    SUMMARIZE('Security Guard Attendance Logs',
        'Security Guard Attendance Logs'[AttendanceReason],  -- Get the incident type from Guards table.
        "IncidentTypeKey", "Guard" & "-" & 'Security Guard Attendance Logs'[AttendanceReason],  -- Create a unique incident type key.
        "TableName", "Guard Data",  -- Specify the table name.
        "Incidents", SUM('Security Guard Attendance Logs'[Number_of_Incidents])  -- Calculate the total incidents.
    )

VAR IncidentTypesPOLICE =
    SUMMARIZE('FACT_Police_Crime_Statistics',
        'FACT_Police_Crime_Statistics'[Crime_Category],  -- Get the incident type from WAPOL table.
        "IncidentTypeKey", "WAPOL" & "-" & 'FACT_Police_Crime_Statistics'[Crime_Category],  -- Create a unique incident type key.
        "TableName", "WAPOL Data",  -- Specify the table name.
        "Incidents", SUM('FACT_Police_Crime_Statistics'[Number_of_Incidents])  -- Calculate the total incidents.
    )

VAR IncidentTypesT1 =
    SUMMARIZE('T1_Requests',
        'T1_Requests'[ram_primary_category],  -- Get the incident type from T1 table.
        "IncidentTypeKey", "T1" & "-" & 'T1_Requests'[ram_primary_category],  -- Create a unique incident type key.
        "TableName", "T1 Requests",  -- Specify the table name.
        "Incidents", SUM('T1_Requests'[Number_of_Incidents])  -- Calculate the total incidents.
    )

-- Combine the calculated tables for each fact table into one table using UNION.
VAR CombinedIncidentTypes =
    UNION(
        IncidentTypesASB,
        IncidentTypesCCTV,
        IncidentTypesGuards,
        IncidentTypesPOLICE,
        IncidentTypesT1
    )

RETURN
    -- Return the 'CombinedIncidentTypes' table that includes incident type, incident type key, table name, and total incidents.
    CombinedIncidentTypes

Thanks in advance for any and all help.

2

So I managed to get some help in figuring this out. The problem was not in the Dax, the Dimension table or the relationships. The problem was in the construction of the ‘IncindetTypeKey’ which was the Primary/Foreign key field. In each table, this was created in power query without a space between words (‘ASB-SuspiciousPerson’) but when I created the dimension table in Dax this was created without removing the space (‘ASB-Suspicious Person’).

This was why it was producing incorrect data when filtered by a field from the DIM table. Some fields didn’t naturally have a space ('ASB-Graffiti’) so the numbers for this were correct whereas it didn’t return the data for those whose IncidentTypeKey did naturally have a space.

:slight_smile:

1 Like