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...
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.