Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
LearnmesomePBI
Frequent Visitor

Identify transactions in different rows in same table based on mulitple criteria

Hello,

 

I have a single table containing overtime transactions (10s of thousands rows per year). Each overtime transaction is in a separate row.  For one specific type of overtime (OT code X), I would like to identify how many times more than 1 person from the same group (ex. East) is doing the same type of overtime on the same day AND at the same time.

 

Here is a sample of my data (jpeg and Excel).  The last column is my expected result.

 

PBI forum sample data for posting.JPG

Here is the formula i have tried which is based upon some other quasi similar posts in the forum (but i realize i may be way off here).  Basically this formula returns (mistakenly) that everything is "Yes".  I didn't even add in the same time criteria based on the OT start and end time yet but that would be an essential piece of this formula as well.

 

OT code type X -More then one person per group at same time (Yes or No) =
if(CALCULATE(COUNTROWS('OT Table'),
FILTER('OT Table', 'OT Table'[Group]=earlier('OT Table'[Group])
&&
'OT Table'[Date]=('OT Table'[Date]))),
"Yes", "No")
 

Any help/guidance would be much appreciated. Thank you in advance.

 

 

1 ACCEPTED SOLUTION
LearnmesomePBI
Frequent Visitor

Sorry, the Excel document did not post for some reason.  here is the same table as the picture in the post.

 

Employee NameOT CodeGroupDateOt start timeOt end timeMultiple people in same group doing Overtime type x at same time?
Mr AXEast2019-01-018:0010:00Yes
Mr BZWest2019-01-0112:0014:00No
Mr CXEast2019-01-019:0011:00Yes
Mr AZWest2019-01-028:0011:00No
Mr BZWest2019-01-028:0012:00No
Mr CYWest2019-01-028:0010:00No
Mr AXWest2019-01-0312:0014:00Yes
Mr BYEast2019-01-039:0011:00No
Mr CXWest2019-01-0312:0014:00Yes

 

View solution in original post

4 REPLIES 4
LearnmesomePBI
Frequent Visitor

Sorry, the Excel document did not post for some reason.  here is the same table as the picture in the post.

 

Employee NameOT CodeGroupDateOt start timeOt end timeMultiple people in same group doing Overtime type x at same time?
Mr AXEast2019-01-018:0010:00Yes
Mr BZWest2019-01-0112:0014:00No
Mr CXEast2019-01-019:0011:00Yes
Mr AZWest2019-01-028:0011:00No
Mr BZWest2019-01-028:0012:00No
Mr CYWest2019-01-028:0010:00No
Mr AXWest2019-01-0312:0014:00Yes
Mr BYEast2019-01-039:0011:00No
Mr CXWest2019-01-0312:0014:00Yes

 

OK @LearnmesomePBI , this turned out to be WAAAAYYYYY more complex than I anticipated going into it. But I think I have a solution. PBIX is attached.

 

Column = 
    VAR __Table = 
        GROUPBY(
            FILTER(
                ALL('Table'),
                [OT Code] = "X"
            ),
            [Group],
            [Date],
            "__Min",MINX(CURRENTGROUP(),'Table'[Ot start time]),
            "__Max",MAXX(CURRENTGROUP(),'Table'[Ot end time])
        )
    VAR __Min = 
        MINX(
            FILTER(
                __Table,
                [Group] = EARLIER([Group]) &&
                    [Date] = EARLIER('Table'[Date])
            ),
            [__Min]
        )
    VAR __Max = 
        MAXX(
            FILTER(
                __Table,
                [Group] = EARLIER([Group]) &&
                    [Date] = EARLIER('Table'[Date])
            ),
            [__Max]
        )
    VAR __Table1 = 
        ADDCOLUMNS(
            'Table',
            "__Count",
            IF([Ot start time] >= __Min || [Ot end time] <= __Max,1,0)
        )
    VAR __Count = 
        SUMX(
            FILTER(
                __Table1,
                [Group] = EARLIER([Group]) &&
                    [Date] = EARLIER([Date])
            ),
            [__Count]
        )
RETURN
    IF([OT Code] <> "X","No",IF(__Count > 1,"Yes", "No"))

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Sorry for my delay in responding.  I have a fairly large file and wanted to "kick the tires" on your proposed solution and see if it was working as intended in all circumstances.  Good news is that it works amazing.

 

I did run into a problem whereby some transaction lines spanned two calendar days.  Ex.  2019-05-24 OT start at 23:00 and finished 01:15. 

PBI_forum_pic_before.JPG

I ended up creating a bunch of conditional columns in the query editor and using the unpivot function to break the transaction out on two lines and then your calculation executed appropriately.

 

PBI_forum_pic_after.JPG

Thanks so much Greg for all of your help.  Wicked solution!

 

 

No problem @LearnmesomePBI ! I actually thought about that scenario when I was developing the DAX code but wasn't up for addressing it at the time! That's a smart solution you found for that, likely way better than trying to account for it in DAX!

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.