Forum Discussion

LearnmesomePBI's avatar
LearnmesomePBI
Frequent Visitor
6 years ago
Solved

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 w...
Need Help
  • LearnmesomePBI's avatar
    LearnmesomePBI
    6 years ago

    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

     

LearnmesomePBI's avatar
LearnmesomePBI
Frequent Visitor
6 years ago

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

 

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

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

     

    • LearnmesomePBI's avatar
      LearnmesomePBI
      Frequent Visitor
      6 years ago

      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. 

      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.

       

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

       

       

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        6 years ago
        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!