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 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:

casenumber5WD_CaseNotes_v6Bins
01567939Between working days 1 and 51
01567939Between working days 11 and 153
01567939Between working days 16 and 204
01567939Between working days 51 and 5511
01567939Between working days 56 and 6012


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.






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.

9 Replies

  • 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. 

         

  • Poojara_D12's avatar
    Poojara_D12
    Icon for Super User rankSuper User
    1 year ago

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

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

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