Forum Discussion

V3Rn3r_8-6's avatar
V3Rn3r_8-6
Frequent Visitor
2 years ago
Solved

DAX formula

Hello, Each row is a single order that was made during a shift that is visible in the "Směna 45-45" column on a specific day that is visible in the "Datum ZN" column and by a specific person by name...
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi V3Rn3r_8-6 

    Use measure instead of calculate column, pbix file attached.

     

     

    Best Regards,

    Jayleny

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi V3Rn3r_8-6 

     

    Please try the following calculate column:

    Realllll = 
VAR _count = CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),'Table'[Name]=EARLIER('Table'[Name])&&'Table'[Date]=EARLIER('Table'[Date])))
RETURN
    IF (
        CALCULATE (
            SUM ( 'Table'[Total Orders on shift] ),
            FILTER (
                ALL ( 'Table' ),
                'Table'[Name] = EARLIER('Table'[Name])
                    && 'Table'[Shift] = "O" &&'Table'[Date]=EARLIER('Table'[Date])
            )
        )
            > CALCULATE (
                SUM ( 'Table'[Total Orders on shift] ),
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Name] = EARLIER('Table'[Name])
                        && 'Table'[Shift] = "N" &&'Table'[Date]=EARLIER('Table'[Date])
                )
            ),
        _count&"x"&"O",
        IF (
            CALCULATE (
                SUM ( 'Table'[Total Orders on shift] ),
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Name] = EARLIER('Table'[Name])
                        && 'Table'[Shift] = "N" &&'Table'[Date]=EARLIER('Table'[Date])
                )
            )
                > CALCULATE (
                    SUM ( 'Table'[Total Orders on shift] ),
                    FILTER (
                        ALL ( 'Table' ),
                        'Table'[Name] = EARLIER('Table'[Name])
                            && 'Table'[Shift] = "R" &&'Table'[Date]=EARLIER('Table'[Date])
                    )
                ),
            _count&"x"&"N",
            _count&"x"&"R"
        )
    )

     

     

     

     

     

     

     

    Best Regards,

    Jayleny

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

V3Rn3r_8-6's avatar
V3Rn3r_8-6
Frequent Visitor
2 years ago

Hello,

Here is an example for what I need, unfortunately just a screen, I haven't figured out how to paste (.pbix). The idea is to list the real shift of a given person on a given day in the Real Shift column. The shifts are counted by eight hours: R = Morning shift 6:00-14:00, O = Midday shift 14:00-22:00 and N Night shift 22:00-6:00. He always makes more warrants for his shift than before or after it, it is max about 1-2 warrants, so depending on which shift he has made the most after calculating in the "Shift" column, it will be written in the "Real Shift" column and so I will achieve that there will be only one shift per person per day. In this example Peter has 1xO and 5xN in the "Shift" column and so he will have 6xN in the "Real Shift" column, Honza has 2xN, 9xR and 1xO and so he will have 12xR in the "Real shift" column , ......


Thank you very much

 

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi V3Rn3r_8-6 

 

Please try the following dax:

Real = 
VAR select_name =
    SELECTEDVALUE ( 'Table'[Name] )
VAR select_shift =
    SELECTEDVALUE ( 'Table'[Shift] )
VAR _count = CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),'Table'[Name]=select_name))
RETURN
    IF (
        CALCULATE (
            SUM ( 'Table'[Total Orders on shift] ),
            FILTER (
                ALL ( 'Table' ),
                'Table'[Name] = select_name
                    && 'Table'[Shift] = "O"
            )
        )
            > CALCULATE (
                SUM ( 'Table'[Total Orders on shift] ),
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Name] = select_name
                        && 'Table'[Shift] = "N"
                )
            ),
        _count&"x"&"O",
        IF (
            CALCULATE (
                SUM ( 'Table'[Total Orders on shift] ),
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Name] = select_name
                        && 'Table'[Shift] = "N"
                )
            )
                > CALCULATE (
                    SUM ( 'Table'[Total Orders on shift] ),
                    FILTER (
                        ALL ( 'Table' ),
                        'Table'[Name] = select_name
                            && 'Table'[Shift] = "R"
                    )
                ),
            _count&"x"&"N",
            _count&"x"&"R"
        )
    )

 

 

Result:

 

 

 

Best Regards,

Jayleny

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

  • V3Rn3r_8-6's avatar
    V3Rn3r_8-6
    Frequent Visitor
    2 years ago

    Hello,

    On this small example, this formula works perfectly, but it doesn't take the date into account at all. When I apply this sample and a larger amount of data where each employee (NAME) enters different shifts each weekday and month, I get the same result everywhere. Can I please still incorporate date counting into this formula? So that it does what this formula does, just for each day separately.

    Thank you very much

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi V3Rn3r_8-6 

      Please try the following DAX:

      Real = 
VAR select_name =
    SELECTEDVALUE ( 'Table'[Name] )
VAR select_shift =
    SELECTEDVALUE ( 'Table'[Shift] )
VAR select_date = 
    SELECTEDVALUE ( 'Table'[Date] )
VAR _count = CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),'Table'[Name]=select_name&&'Table'[Date]=select_date))
RETURN
    IF (
        CALCULATE (
            SUM ( 'Table'[Total Orders on shift] ),
            FILTER (
                ALL ( 'Table' ),
                'Table'[Name] = select_name
                    && 'Table'[Shift] = "O" &&'Table'[Date]=select_date
            )
        )
            > CALCULATE (
                SUM ( 'Table'[Total Orders on shift] ),
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Name] = select_name
                        && 'Table'[Shift] = "N" &&'Table'[Date]=select_date
                )
            ),
        _count&"x"&"O",
        IF (
            CALCULATE (
                SUM ( 'Table'[Total Orders on shift] ),
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Name] = select_name
                        && 'Table'[Shift] = "N" &&'Table'[Date]=select_date
                )
            )
                > CALCULATE (
                    SUM ( 'Table'[Total Orders on shift] ),
                    FILTER (
                        ALL ( 'Table' ),
                        'Table'[Name] = select_name
                            && 'Table'[Shift] = "R" &&'Table'[Date]=select_date
                    )
                ),
            _count&"x"&"N",
            _count&"x"&"R"
        )
    )

       

       

       

       

       

       

       

      Best Regards,

      Jayleny

       

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      • V3Rn3r_8-6's avatar
        V3Rn3r_8-6
        Frequent Visitor
        2 years ago

        Hello,

        Please, when I apply this formula to my spreadsheet, I get the same result in all rows, namely "xR". What could be the error?


        Thank you very much