Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
V3Rn3r_8-6
Regular Visitor

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 in the "Name" column. In the "Total Orders on shift" column you can see how many orders occurred in the same shift. I need the "real shift" column to show the letter "R", "O" or "N" in each row depending on which shift had the most orders for that day and that person. That is, in this case, the "Směna 45-45" column would have the letters "N" in each row.

 

V3Rn3r_86_0-1720017969319.png

 

Thank you very much

 

1 ACCEPTED SOLUTION

Hi @V3Rn3r_8-6 

Use measure instead of calculate column, pbix file attached.


vjialongymsft_0-1721118221119.png

 

 

Best Regards,

Jayleny

 

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

View solution in original post

12 REPLIES 12
V3Rn3r_8-6
Regular Visitor

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

Exaple Real shift screen.png

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:

vjialongymsft_0-1720513939007.png

 

 

 

Best Regards,

Jayleny

 

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

 

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

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.

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

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.

Hi @V3Rn3r_8-6 

Use measure instead of calculate column, pbix file attached.


vjialongymsft_0-1721118221119.png

 

 

Best Regards,

Jayleny

 

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

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!

 

 

v-jialongy-msft
Community Support
Community Support

Hi @V3Rn3r_8-6 

 

Please try the following measure:

HReal = 
VAR select_date =
    SELECTEDVALUE ( 'Table'[Datum ZN] )
VAR select_smena =
    SELECTEDVALUE ( 'Table'[Smena 45-45] )
RETURN
    IF (
        CALCULATE (
            SUM ( 'Table'[Total Orders on shift] ),
            FILTER (
                ALL ( 'Table' ),
                'Table'[Datum ZN] = select_date
                    && 'Table'[Smena 45-45] = "O"
            )
        )
            > CALCULATE (
                SUM ( 'Table'[Total Orders on shift] ),
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Datum ZN] = select_date
                        && 'Table'[Smena 45-45] = "N"
                )
            ),
        "O",
        IF (
            CALCULATE (
                SUM ( 'Table'[Total Orders on shift] ),
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Datum ZN] = select_date
                        && 'Table'[Smena 45-45] = "N"
                )
            )
                > CALCULATE (
                    SUM ( 'Table'[Total Orders on shift] ),
                    FILTER (
                        ALL ( 'Table' ),
                        'Table'[Datum ZN] = select_date
                            && 'Table'[Smena 45-45] = "R"
                    )
                ),
            "N",
            "R"
        )
    )

 

 

 

Result:

vjialongymsft_0-1720058714218.png

 

 

 

 

 

 

 

Best Regards,

Jayleny

 

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

Hello,

Thank you very much for your help, only in this case it always returns "R" because every day most orders are always made on the morning shift. I would need if it would be possible to use another column with the names of the people, so that the shift is listed separately for each of them. If someone has a night shift and makes an order on the afternoon shift, have this formula list the night shift everywhere as I wrote before and do the same for different people and different shifts.

("R" morning shift, "O" afternoon shift, "N" night shift)

Thank you very much

Hi @V3Rn3r_8-6 

 

Please provide sample data that fully covers your issue and the expected outcome based on the sample data you provided.

 

 

 

 

 

Best Regards,

Jayleny

 

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.