Forum Discussion

jhauw74's avatar
jhauw74
Icon for Helper I rankHelper I
1 year ago
Solved

Identifying pass/fail based on binnings

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...
Need Help
  • jhauw74's avatar
    jhauw74
    1 year ago

    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.

rajendraongole1's avatar
rajendraongole1
Icon for Super User rankSuper User
1 year ago

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's avatar
jhauw74
Icon for Helper I rankHelper I
1 year ago

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's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
      jhauw74
      Icon for Helper I rankHelper I
      1 year ago

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

      Thanking you in advance for helping this.