DAX formula

[V3Rn3r_8-6]

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.

Thank you very much

[v-jialongy-msft · Accepted solution]

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.

[V3Rn3r_8-6]

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

 

[v-jialongy-msft]

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]

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

[v-jialongy-msft]

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]

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

[v-jialongy-msft]

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.

[V3Rn3r_8-6]

https://drive.google.com/file/d/1HI8PfesdkneNfe271IKbFK68xtvh22ei/view?usp=sharing

[v-jialongy-msft · Accepted solution]

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.

[V3Rn3r_8-6]

Hello,

Please, could it be done so that the calculation is in a new column in Table view instead of the way it is now as a new measurement? The way it is, when I apply it to the original table, it takes too long to load because of the large amount of data and especially I need to use this result to filter different data

Thank you very much.

[v-jialongy-msft · Accepted solution]

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]

Hello,

This is just what I needed, you're absolutely brilliant, thank you very much.

Great job, I'll be praising you everywhere.

Thank you very much again

[foodd]

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]

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:

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]

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

[v-jialongy-msft]

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.