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.

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi V3Rn3r_8-6 

         

        Please provide sample data that fully covers your issue and the expected outcome based on the sample data you provided.(In the form of a table or PBIX file, not a screenshot)

         

         

         

         

         

        Best Regards,

        Jayleny

         

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