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

 

foodd's avatar
foodd
Community Champion
2 years ago

Hello V3Rn3r_8-6 , in order to share your work-in-progress Power BI Desktop file (with sensitive information removed) and any source files in Excel format that fully address your issue or question in a usable format (not as a screenshot). You can upload these files to a cloud storage service such as OneDrive, Google Drive, Dropbox, or to a Github repository, and then provide the file's URL.

If your requirement is solved, please make THIS ANSWER a SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you.   Proud to be a Super User!