Identifying pass/fail based on binnings

[jhauw74]

Hello expert,

I am stumble on how I can create a dax that'll able to identify pass/fail based on the intervals.

I have a data set that has various cases ID that has been binned into categories like below:

Between working days -4 and 0

Between working days 1 and 5

Between working days 6 and 10

Between working days 11 and 15

Between working days 16 and 20

Between working days 21 and 25

Between working days 26 and 30

Between working days 31 and 35

Between working days 36 and 40

Between working days 41 and 45

Between working days 46 and 50

Between working days 51 and 55

Between working days 56 and 60

Between working days 61 and 65

Between working days 66 and 70

Between working days 71 and 75

Between working days 76 and 80

Between working days 81 and 85

Between working days 86 and 90

Between working days 91 and 95

Between working days 96 and 100

Between working days 101 and 105

Between working days 106 and 110

Between working days 111 and 115

Between working days 116 and 120

Between working days 121 and 125

Between working days 126 and 130

Between working days 131 and 135

Between working days 136 and 140

Between working days 141 and 145

Between working days 146 and 150

Between working days 151 and 155

Between working days 156 and 160

Between working days 161 and 165

Between working days 166 and 170

Between working days 171 and 175

Between working days 181 and 185

Between working days 186 and 190

Between working days 191 and 195

Between working days 196 and 200

Between working days 201 and 205

Between working days 206 and 210

Between working days 211 and 215

Between working days 221 and 225

Between working days 231 and 235

Between working days 236 and 240

Between working days 241 and 245

Between working days 256 and 260

Between working days 261 and 265

I have created the custom table that will enable the sequential numeric from 1 - 48.
The result that I after is,  if there's missing sequential like below:

casenumber	5WD_CaseNotes_v6	Bins
01567939	Between working days 1 and 5	1
01567939	Between working days 11 and 15	3
01567939	Between working days 16 and 20	4
01567939	Between working days 51 and 55	11
01567939	Between working days 56 and 60	12

The dax should able to identify that there's missing sequential number eg. sequential #2, sequential#5-10 as 'Fail', or 'Pass' if there's no missing sequential numbers.

I have tried using chatgpt resulting below dax:

Missing_Bins =

VAR CaseID = SELECTEDVALUE(Summary_HistoryCases[casenumber])

-- Get all existing bins for this case

VAR ExistingBins =

    CALCULATETABLE(

        DISTINCT(Summary_HistoryCases[Bins]),

        Summary_HistoryCases[casenumber] = CaseID

    )

-- Ensure bins exist before finding min and max

VAR MinBin = IF(COUNTROWS(ExistingBins) > 0, MINX(ExistingBins, Summary_HistoryCases[Bins]), BLANK())

VAR MaxBin = IF(COUNTROWS(ExistingBins) > 0, MAXX(ExistingBins, Summary_HistoryCases[Bins]), BLANK())

-- Generate the expected bins only if MinBin and MaxBin are valid

VAR ExpectedBins =

    IF(

        NOT(ISBLANK(MinBin)) && NOT(ISBLANK(MaxBin)),

        GENERATESERIES(MinBin, MaxBin, 1),

        BLANK()

    )

-- Convert ExpectedBins to a table with a named column

VAR ExpectedBinsTable =

    IF(

        NOT(ISBLANK(ExpectedBins)),

        SELECTCOLUMNS(ExpectedBins, "Value", [Value]),

        BLANK()

    )

-- Find missing bins only if ExpectedBinsTable is not blank

VAR MissingBins =

    IF(

        NOT(ISBLANK(ExpectedBinsTable)),

        EXCEPT(

            ExpectedBinsTable,

            ExistingBins

        ),

        BLANK()

    )

RETURN

    IF(NOT(ISBLANK(MissingBins)) && COUNTROWS(MissingBins) > 0, "Missing Bins Detected", "No Missing Bins")

But fails to generate the desirable results unfortunately.

Would appreciate any help thank you in advance.

[jhauw74 · Accepted solution]

Hi - thanks for all your help. 

I found the solution by creating a sequential mapping in excel - then I added up each of the sequential as a sum eg. Bins 1 --> 1, Bins 2 -->2, Bins 3--> 3 then to sum it and make it as ID eg. 1+0 = 1, 1+2= 3, 1+2+3 =6 and so on. This will identify if there's any missing gaps throughout the bins's sequence.

[Poojara_D12]

Hi @jhauw74 

You can achieve this by identifying the missing sequential bins for each casenumber. The key is to compare the existing bins with a generated series of expected bins and determine if any numbers are missing.

Pass_Fail_Status = 
VAR CaseID = SELECTEDVALUE(Summary_HistoryCases[casenumber])

-- Get existing bins for the selected case
VAR ExistingBins =
    CALCULATETABLE(
        DISTINCT(Summary_HistoryCases[Bins_Sequence]), 
        Summary_HistoryCases[casenumber] = CaseID
    )

-- Find the minimum and maximum bin sequence numbers
VAR MinBin = MINX(ExistingBins, Summary_HistoryCases[Bins_Sequence])
VAR MaxBin = MAXX(ExistingBins, Summary_HistoryCases[Bins_Sequence])

-- Generate the expected full sequence within the range
VAR ExpectedBins = GENERATESERIES(MinBin, MaxBin, 1)

-- Identify missing bins by comparing expected vs. actual
VAR MissingBins = EXCEPT(ExpectedBins, ExistingBins)

-- Check if any missing bins exist
VAR MissingCount = COUNTROWS(MissingBins)

RETURN 
    IF(MissingCount > 0, "Fail", "Pass")

[jhauw74]

Thanks @Poojara_D12 - @rajendraongole1 has provided similar refined DAX unfortunately it doesn't work as giving this error : "The arguments in GenerateSeries function cannot be blank" 

[rajendraongole1]

Can you please try the below 

Missing_Bins =
VAR CaseID = SELECTEDVALUE(Summary_HistoryCases[casenumber])

-- Get the unique bin numbers for this Case ID
VAR ExistingBins =
CALCULATETABLE(
VALUES(Summary_HistoryCases[Bins]),
Summary_HistoryCases[casenumber] = CaseID
)

-- Ensure there are existing bins before calculating min/max
VAR MinBin = MINX(ExistingBins, Summary_HistoryCases[Bins])
VAR MaxBin = MAXX(ExistingBins, Summary_HistoryCases[Bins])

-- Handle blank MinBin/MaxBin by setting defaults
VAR SafeMinBin = IF(ISBLANK(MinBin), 0, MinBin) -- Default to 0 if blank
VAR SafeMaxBin = IF(ISBLANK(MaxBin), 0, MaxBin) -- Default to 0 if blank

-- Generate the expected sequence only if valid bins exist
VAR ExpectedBins =
IF(SafeMinBin <> 0 && SafeMaxBin <> 0,
GENERATESERIES(SafeMinBin, SafeMaxBin, 1),
BLANK()
)

-- Find missing bins only if ExpectedBins is not blank
VAR MissingBins =
IF(
NOT(ISBLANK(ExpectedBins)),
EXCEPT(
ExpectedBins,
ExistingBins
),
BLANK()
)

-- Return "Fail" if there are missing bins, "Pass" if all bins exist
RETURN
IF(SafeMinBin = 0 || SafeMaxBin = 0, "No Data",
IF(NOT(ISBLANK(MissingBins)) && COUNTROWS(MissingBins) > 0, "Fail", "Pass")
)

still issue exist,please let me know.

[jhauw74]

Thanks @rajendraongole1 - unfortunately still not working 😪

[rajendraongole1]

Hi @jhauw74  - Your DAX needs a better way to compare the existing bins against the expected sequence to detect gaps properly. 

Modified dax for your reference below:

Missing_Bins =
VAR CaseID = SELECTEDVALUE(Summary_HistoryCases[casenumber])

-- Get all existing bins for this case
VAR ExistingBins =
CALCULATETABLE(
VALUES(Summary_HistoryCases[Bins]),
Summary_HistoryCases[casenumber] = CaseID
)

-- Get the minimum and maximum bin numbers for this case
VAR MinBin = MINX(ExistingBins, Summary_HistoryCases[Bins])
VAR MaxBin = MAXX(ExistingBins, Summary_HistoryCases[Bins])

-- Generate the expected sequence of bins between MinBin and MaxBin
VAR ExpectedBins =
GENERATESERIES(MinBin, MaxBin, 1)

-- Compare expected bins against existing bins
VAR MissingBins =
EXCEPT(ExpectedBins, ExistingBins)

-- Return Pass/Fail based on missing bins
RETURN
IF(COUNTROWS(MissingBins) > 0, "Fail", "Pass")

If your Bins column contains text instead of numeric values (e.g., "Between working days 1 and 5"), you'll need to map them to numbers in a separate table for accurate comparisons.
Ensure that the Bins column has been converted to a numeric sequence in your dataset.

[jhauw74]

@rajendraongole1  thank you for helping 🙂

I've tested the modified DAX but it generates error : "The arguments in GenerateSeries function cannot be blank."
In my 'bins' column - they are all identified as a whole number.

Also this table is basically created using summarize dax :

Summary_HistoryCases =

SUMMARIZE(

    FILTER(

        Case_History,

        NOT(ISBLANK(Case_History[casenumber])) &&

        NOT(ISBLANK(Case_History[createddate_])) &&

        NOT(ISBLANK(Case_History[lastmodifieddate]))

    ),

    Case_History[casenumber],

    --Case_History[createddate_],

    --Case_History[lastmodifieddate],

    Case_History[5WD_CaseNotes_v6]

)

Below is the snapshot of the summarize table:

The Bins column coming from an excel where I perform the binning based on the sort of value ie. between working days 1 and 5 = 1, working days 6 and 10 = 2, and so forth.

I wonder if the DAX doesn't work because of the above? 

[rajendraongole1]

Hi @jhauw74  - The Bins column isn't properly recognized as numbers in the DAX measure.

modified DAX measure:

Missing_Bins =
VAR CaseID = SELECTEDVALUE(Summary_HistoryCases[casenumber])

-- Get the unique bin numbers for this Case ID
VAR ExistingBins =
CALCULATETABLE(
VALUES(Summary_HistoryCases[Bins]),
Summary_HistoryCases[casenumber] = CaseID
)

-- Ensure there are existing bins before calculating min/max
VAR MinBin = IF(COUNTROWS(ExistingBins) > 0, MINX(ExistingBins, Summary_HistoryCases[Bins]), BLANK())
VAR MaxBin = IF(COUNTROWS(ExistingBins) > 0, MAXX(ExistingBins, Summary_HistoryCases[Bins]), BLANK())

-- If either MinBin or MaxBin is blank, return "No Data"
VAR BinCheck =
IF(ISBLANK(MinBin) || ISBLANK(MaxBin), "No Data", "Proceed")

-- Generate the expected sequence only if valid bins exist
VAR ExpectedBins =
IF(BinCheck = "Proceed",
GENERATESERIES(MinBin, MaxBin, 1),
BLANK()
)

-- Find missing bins only if ExpectedBins is not blank
VAR MissingBins =
IF(
NOT(ISBLANK(ExpectedBins)),
EXCEPT(
ExpectedBins,
ExistingBins
),
BLANK()
)

-- Return "Fail" if there are missing bins, "Pass" if all bins exist
RETURN
IF(BinCheck = "No Data", "No Data",
IF(NOT(ISBLANK(MissingBins)) && COUNTROWS(MissingBins) > 0, "Fail", "Pass")
)

Apply this revised DAX measure. and let me know if still issue exist. 

[jhauw74]

Thanks @rajendraongole1 - I just applied it but there's still an error below:

Thanking you in advance for helping this.