Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Group column values based on condition

As per screenshot of table below, in the "Job to Accep Elapsed Time (Days)" column, how do I group values greater than 10? Expected value when DAYS >= 10, show "10+" instead. Hope that makes sense. ...
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    Please check the below picture and the attached pbix file.

     

     

     

    Total jobs by group : =
    CALCULATE (
    [Total Jobs :],
    FILTER (
    VALUES ( Data[Category] ),
    COUNTROWS (
    FILTER (
    GroupTable,
    IF (
    GroupTable[Max] <> BLANK (),
    [Days :] >= GroupTable[Min]
    && [Days :] <= GroupTable[Max],
    [Days :] >= GroupTable[Min]
    )
    )
    ) > 0
    )
    )

     

     

Jihwan_Kim's avatar
Jihwan_Kim
Super User
4 years ago

Hi, 

I do not know how your data model looks like, but check the below picture and the attached pbix file.

 

 

Days by group : =
CALCULATE (
VALUES ( GroupTable[Group] ),
FILTER (
GroupTable,
IF (
GroupTable[Max] <> BLANK (),
[Days :] >= GroupTable[Min]
&& [Days :] <= GroupTable[Max],
[Days :] >= GroupTable[Min]
)
)
)

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Jihwan_Kim   sorry I wasn't clear with my explanation and didn't provide enough information.

    My expected result is to sum all jobs greater than 10 days into 10+ so as per screenshot sample below, the result will be (245+205+190+69+126+105) final result on table and chart will show 10+ = 940

    Fyi the measures for the two columns in table:

    TotalFailedJobsNextDay = CALCULATE(COUNTROWS('FreightForward v2'),FILTER('FreightForward v2','FreightForward v2'[BOOKING_BEFORE_AFTER_12]="AFTER 12" &&'FreightForward v2'[COMPLY_COMMENT]=BLANK() &&'FreightForward v2'[JOBCOUNT]=1))

=====================================================================================

Job to Accep Elapsed TIme Days = (DATEDIFF('FreightForward v2'[JOB_BOOKING_DATETIME],'FreightForward v2'[ACCEPTANCE_DATETIME],DAY ))

    Hope that makes sense.

    • Jihwan_Kim's avatar
      Jihwan_Kim
      Super User
      4 years ago

      Hi,

      Please check the below picture and the attached pbix file.

       

       

       

      Total jobs by group : =
      CALCULATE (
      [Total Jobs :],
      FILTER (
      VALUES ( Data[Category] ),
      COUNTROWS (
      FILTER (
      GroupTable,
      IF (
      GroupTable[Max] <> BLANK (),
      [Days :] >= GroupTable[Min]
      && [Days :] <= GroupTable[Max],
      [Days :] >= GroupTable[Min]
      )
      )
      ) > 0
      )
      )

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Jihwan_Kimawesome that worked as expected. Many thanks for your help, much appreciated