cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DebbieE Senior Member
Senior Member

DAX to Group Hours after a Distinct count has been applied

I have data like this

 

OrgID Event Hours 

1           1      4

1           2      4

2           1      1

3           1       0

4           1       5

5           1       7

1           3      4

2          2       2   

3          2       1

 

And I want to group on Hours After Ive counted all the distinct organisations. So for example

 

OrgID  Hours Group

1            12     12 or Over

2            3        0 to 3 hours

3            2        0 to 3 hours

4            5        3 to 6 houur

5           7         6 to 12 hours

 

And then you could have a column chart showing the number of of organisations against each hour. 

 

But I just cant find an easy way to do it. I tried this DAX

EngagedHours = 
IF(SUM(Activity[Hours]) >= 0.5 && SUM(Activity[Hours]) < 6,
"Number of 0.5 to 6 hours",
IF(SUM(Activity[Hours]) >= 6 && SUM(Activity[Hours]) < 9,
"Number on 6 to 9 hours",
IF(SUM(Activity[Hours]) >= 9 && SUM(Activity[Hours]) < 12,
"Number on 9 to 11.9 hours",
IF(SUM(Activity[Hours]) >= 12,"Number of 12 hours or above",
"NA")
)))

But it just doesnt work when I take the OrgIDs out. It just shows me everything is over 12 hours. I didnt do a simple conditional column in Power Query because the rows have to be aggregated to the total hours for each org. It cant be done by row.  Is there any way of doing this fairly easily?

1 ACCEPTED SOLUTION

Accepted Solutions
LivioLanzo Super Contributor
Super Contributor

Re: DAX to Group Hours after a Distinct count has been applied

Hi @DebbieE 

 

see the file I have uploaded: https://1drv.ms/u/s!AiiWkkwHZChHj1qWUi6jqgRzc_ZR

 

 

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

View solution in original post

1 REPLY 1
LivioLanzo Super Contributor
Super Contributor

Re: DAX to Group Hours after a Distinct count has been applied

Hi @DebbieE 

 

see the file I have uploaded: https://1drv.ms/u/s!AiiWkkwHZChHj1qWUi6jqgRzc_ZR

 

 

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

View solution in original post

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 378 members 3,389 guests
Please welcome our newest community members: