cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
qsmith83
Post Partisan
Post Partisan

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.

@wdx223_Daniel @Jihwan_Kim - appreciate any suggestions

 

Screenshot 2021-10-14 123336.jpg

1 ACCEPTED SOLUTION

Hi,

Please check the below picture and the attached pbix file.

 

 

Picture1.png

 

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

 

 


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

4 REPLIES 4
Jihwan_Kim
Super User
Super User

Hi, 

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

 

Picture1.png

 

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

 


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

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

Screenshot 2021-10-15 110425.jpg

Hi,

Please check the below picture and the attached pbix file.

 

 

Picture1.png

 

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

 

 


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

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

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors