Data modelling suggestions

[vibhoryadav23]

I have a dataset in which the base of my calculations dynamically changes based on various filters. Some of these filters can directly be applied but some of these are coming from other tables which cannot be directly mapped. For example:

This is the sample dataset:

Date	ID	TNr	RT	Error
11/10/2024	X	1	1	OK
11/10/2024	Y	2	2	OK
11/10/2024	Z	3	1	OK
11/11/2024	Z	4	2	OK
11/11/2024	X	5	1	OK
11/11/2024	X	6	2	OK
11/12/2024	X	1	2	d
11/12/2024	X	2	1	e
11/12/2024	X	3	2	x
11/13/2024	Y	4	1	y
11/13/2024	Y	5	2	z
11/13/2024	Z	6	1	OK
11/14/2024	X	1	2	e
11/14/2024	Y	2	1	e
11/14/2024	Z	3	2	OK
11/15/2024	Z	4	2	OK
11/15/2024	X	5	1	OK
11/15/2024	X	6	2	OK
11/16/2024	X	1	1	OK
11/16/2024	X	2	2	e
11/16/2024	X	3	1	y
11/17/2024	Y	4	2	z
11/17/2024	Y	5	1	r

I want to find the total % of Error by each value in "Error" field. For example: In the dataset above, 'e' appears 4 times and total rows are 23, hence, its 17%. All values in "Error" column will be displayed in the column chart with their respective % value.

This calculation should also change on the bases of the filter applied on the other columns (there can be more in the actual data).

Now the catch is, there are several conditions on the data which should also be a part of slicer/filter (user should have ability to chose any of these conditions and any of the filter from the actual data):

Scenario	Condition
A	All
B	TNr = 1
C	TNr = 2 and RT = 1
D	TNR >=2 and RT = 1

Also, there needs to be a functionality where user is able to hide just the "OK" values from the chart displaying Error % but without affecting the original calculation (as number of "OK" values are high is is causing skewness in the data. Hence we need to remove it from the chart view but still being calculated). For example:

[Anonymous]

Hi @vibhoryadav23 

You can try using the ALLEXCEPT() function to keep the filters you want in the table.

1. Create a calculated column to represent the different conditions:

Condition = SWITCH(
    TRUE(),
    'Table'[TNr] = 1, "B",
    'Table'[TNr] = 2 && 'Table'[RT] = 1, "C",
    'Table'[TNr] >= 2 && 'Table'[RT] = 1, "D",
    BLANK()
)

Create a slicer with calculated columns, check the Select All option in the slicer settings and apply “is not blank” in the visual filter panel. "Select all” means condition A.

2. Create a measure to calculate the error message percentage:

(If you have slicers for other fields, add the appropriate fields to the ALLEXCEPT() function body.)

Error % = 
VAR TotalErrors = CALCULATE(
    COUNT('Table'[Error]), 
    ALLEXCEPT('Table', 'Table'[Date], 'Table'[Condition])
)
RETURN
SUMX(
    VALUES('Table'[Error]),
    VAR CurrentError = [Error]
    VAR ErrorCount = CALCULATE(
        COUNT('Table'[Error]), 
        'Table'[Error] = CurrentError,
        ALLEXCEPT('Table', 'Table'[Date], 'Table'[Condition])
    )
    RETURN
    IF(
        CurrentError <> "OK",
        DIVIDE(ErrorCount, TotalErrors),
        BLANK()
    )
)

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

[rajendraongole1 · Accepted solution]

Hi @vibhoryadav23 - you can write measures to dynamically calculate the percentage of each error while considering the applied filters.

create total count of Errors

Total Errors =
CALCULATE(
COUNT('Table'[Error]),
ALL('Table'[Error]) -- Ensures all errors are considered, even if some are filtered
)

create percentage of Each Error

Error % =
VAR CurrentErrorCount =
COUNT('Table'[Error])
RETURN
DIVIDE(CurrentErrorCount, [Total Errors], 0) * 100

Create a measure that applies the selected scenario dynamically

Scenario Filter =
SWITCH(
SELECTEDVALUE('Scenario Table'[Scenario]),
"A", 1, -- No filter applied
"B", IF('Table'[TNr] = 1, 1, 0),
"C", IF('Table'[TNr] = 2 && 'Table'[RT] = 1, 1, 0),
"D", IF('Table'[TNr] >= 2 && 'Table'[RT] = 1, 1, 0),
1 -- Default to no filter
)

To exclude "OK" values from the chart while keeping them in calculations

Error % for Chart =
IF(
MAX('Table'[Error]) = "OK",
BLANK(), -- Exclude "OK" from the chart
[Error %] -- Show percentage for other errors
)

Use a bar chart to display the percentages for each error.

Set the Error field on the X-axis.
Use the Error % for Chart measure as the Y-axis value.
Add a slicer for the Scenario Table so users can select the desired scenario.

Add any other slicers (e.g., for Date, ID, etc.) to allow users to filter the data.

Hope this works. please try

[vibhoryadav23]

Hi @rajendraongole1 , This is amazing!

Everything works well except this Measure for some reason:

PS: Ignore the space in "T nr". This is how it was in my data.

[rajendraongole1]

can you please check the below corrected measure:

Scenario Filter =
SWITCH(
SELECTEDVALUE('Scenario Table'[Scenario]),
"A", 1, -- No filter applied
"B", IF(MAX('Table'[TNr]) = 1, 1, 0),
"C", IF(MAX('Table'[TNr]) = 2 && MAX('Table'[RT]) = 1, 1, 0),
"D", IF(MAX('Table'[TNr]) >= 2 && MAX('Table'[RT]) = 1, 1, 0),
1 -- Default to no filter
)

[vibhoryadav23]

It still doesnt work.

Just to rephrase the requirement: When an option is selected from slicer, the dataset should filter on the respective conditions, with other filters still being applicable. In the dataset above, if I select scenario "B" from filter, then it should filter the dataset on TNr = 1:

But this has to be done in a way that other filters are still applicable. For example, user can further filter data on 'Date' or other columns in the data for all visuals in the dashboards

[rajendraongole1]

Hi @vibhoryadav23 - I hope this works as per your description

Then create a dynamically applies the condition based on the selected scenario. Here’s an updated version of the measure

Scenario Filter =
SWITCH(
SELECTEDVALUE('Scenario Table'[Scenario]),
"A", 1, -- No filter
"B", IF(SELECTEDVALUE('Table'[TNr]) = 1, 1, 0),
"C", IF(SELECTEDVALUE('Table'[TNr]) = 2 && SELECTEDVALUE('Table'[RT]) = 1, 1, 0),
"D", IF(SELECTEDVALUE('Table'[TNr]) >= 2 && SELECTEDVALUE('Table'[RT]) = 1, 1, 0),
1 -- Default: No filter
)

[vibhoryadav23]

Sorry but still doesnt work. Same result. Its not doing anything to the data at all