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.

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

        Thanks rajendraongole1 - unfortunately still not working ðŸ˜ª