Need simplification on this DAX code in Power BI

[Samuelroark01]

I am attempting to introduce a PPH Value to ALL stations that begin with "Core Table". The PPH value varies at different times of the day to account for break times. All stations will have the same PPH values throughout the day.

The usual PPH Value is 162.

Variations of this Value occur at:

4 am = 121

6 am = 121

10 am = 81

2 pm = 121

5 pm = 81

9 pm = 121

all other hours throughout the day will be 162 PPH.

Is there a way to say: "For all stations that contain the words Core Table, assign the PPH value above at the hours listed above, every other hour assign value 162"

Here is a sample of my current code. I can't add any additional stations to the code because power BI says the query is complex. Condensing it down is the only way I will be able to add additional stations.

Core PPH = if(and('Data Complete'[Station]="Core Table 2",'Data Complete'[Start of Hour]=VALUE("4:00:00 AM")),121,

            if(and('Data Complete'[Station]="Core Table 2",'Data Complete'[Start of Hour]=VALUE("5:00:00 AM")),162,

            if(and('Data Complete'[Station]="Core Table 2",'Data Complete'[Start of Hour]=VALUE("6:00:00 AM")),121,

            if(and('Data Complete'[Station]="Core Table 2",'Data Complete'[Start of Hour]=VALUE("7:00:00 AM")),162,

            if(and('Data Complete'[Station]="Core Table 2",'Data Complete'[Start of Hour]=VALUE("8:00:00 AM")),162,

            if(and('Data Complete'[Station]="Core Table 2",'Data Complete'[Start of Hour]=VALUE("9:00:00 AM")),162,

            if(and('Data Complete'[Station]="Core Table 2",'Data Complete'[Start of Hour]=VALUE("10:00:00 AM")),81,

            if(and('Data Complete'[Station]="Core Table 2",'Data Complete'[Start of Hour]=VALUE("11:00:00 AM")),162,

            if(and('Data Complete'[Station]="Core Table 2",'Data Complete'[Start of Hour]=VALUE("12:00:00 PM")),162,

            if(and('Data Complete'[Station]="Core Table 2",'Data Complete'[Start of Hour]=VALUE("1:00:00 PM")),162,

            if(and('Data Complete'[Station]="Core Table 2",'Data Complete'[Start of Hour]=VALUE("2:00:00 PM")),121,

            if(and('Data Complete'[Station]="Core Table 2",'Data Complete'[Start of Hour]=VALUE("3:00:00 PM")),162,

            if(and('Data Complete'[Station]="Core Table 2",'Data Complete'[Start of Hour]=VALUE("4:00:00 PM")),162,

            if(and('Data Complete'[Station]="Core Table 2",'Data Complete'[Start of Hour]=VALUE("5:00:00 PM")),81,

            if(and('Data Complete'[Station]="Core Table 2",'Data Complete'[Start of Hour]=VALUE("6:00:00 PM")),162,

            if(and('Data Complete'[Station]="Core Table 2",'Data Complete'[Start of Hour]=VALUE("7:00:00 PM")),162,

            if(and('Data Complete'[Station]="Core Table 2",'Data Complete'[Start of Hour]=VALUE("8:00:00 PM")),162,

            if(and('Data Complete'[Station]="Core Table 2",'Data Complete'[Start of Hour]=VALUE("9:00:00 PM")),121,

            if(and('Data Complete'[Station]="Core Table 2",'Data Complete'[Start of Hour]=VALUE("10:00:00 PM")),162,

            if(and('Data Complete'[Station]="Core Table 2",'Data Complete'[Start of Hour]=VALUE("11:00:00 PM")),162,

                if(and('Data Complete'[Station]="Core Table 3",'Data Complete'[Start of Hour]=VALUE("4:00:00 AM")),121,

                if(and('Data Complete'[Station]="Core Table 3",'Data Complete'[Start of Hour]=VALUE("5:00:00 AM")),162,

                if(and('Data Complete'[Station]="Core Table 3",'Data Complete'[Start of Hour]=VALUE("6:00:00 AM")),121,

                if(and('Data Complete'[Station]="Core Table 3",'Data Complete'[Start of Hour]=VALUE("7:00:00 AM")),162,

                if(and('Data Complete'[Station]="Core Table 3",'Data Complete'[Start of Hour]=VALUE("8:00:00 AM")),162,

                if(and('Data Complete'[Station]="Core Table 3",'Data Complete'[Start of Hour]=VALUE("9:00:00 AM")),162,

                if(and('Data Complete'[Station]="Core Table 3",'Data Complete'[Start of Hour]=VALUE("10:00:00 AM")),81,

                if(and('Data Complete'[Station]="Core Table 3",'Data Complete'[Start of Hour]=VALUE("11:00:00 AM")),162,

                if(and('Data Complete'[Station]="Core Table 3",'Data Complete'[Start of Hour]=VALUE("12:00:00 PM")),162,

                if(and('Data Complete'[Station]="Core Table 3",'Data Complete'[Start of Hour]=VALUE("1:00:00 PM")),162,

                if(and('Data Complete'[Station]="Core Table 3",'Data Complete'[Start of Hour]=VALUE("2:00:00 PM")),121,

                if(and('Data Complete'[Station]="Core Table 3",'Data Complete'[Start of Hour]=VALUE("3:00:00 PM")),162,

                if(and('Data Complete'[Station]="Core Table 3",'Data Complete'[Start of Hour]=VALUE("4:00:00 PM")),162,

                if(and('Data Complete'[Station]="Core Table 3",'Data Complete'[Start of Hour]=VALUE("5:00:00 PM")),81,

                if(and('Data Complete'[Station]="Core Table 3",'Data Complete'[Start of Hour]=VALUE("6:00:00 PM")),162,

                if(and('Data Complete'[Station]="Core Table 3",'Data Complete'[Start of Hour]=VALUE("7:00:00 PM")),162,

                if(and('Data Complete'[Station]="Core Table 3",'Data Complete'[Start of Hour]=VALUE("8:00:00 PM")),162,

                if(and('Data Complete'[Station]="Core Table 3",'Data Complete'[Start of Hour]=VALUE("9:00:00 PM")),121,

                if(and('Data Complete'[Station]="Core Table 3",'Data Complete'[Start of Hour]=VALUE("10:00:00 PM")),162,

                if(and('Data Complete'[Station]="Core Table 3",'Data Complete'[Start of Hour]=VALUE("11:00:00 PM")),162,

                    if(and('Data Complete'[Station]="Core Table 4",'Data Complete'[Start of Hour]=VALUE("4:00:00 AM")),121,

                    if(and('Data Complete'[Station]="Core Table 4",'Data Complete'[Start of Hour]=VALUE("5:00:00 AM")),162,

                    if(and('Data Complete'[Station]="Core Table 4",'Data Complete'[Start of Hour]=VALUE("6:00:00 AM")),121,

                    if(and('Data Complete'[Station]="Core Table 4",'Data Complete'[Start of Hour]=VALUE("7:00:00 AM")),162,

                    if(and('Data Complete'[Station]="Core Table 4",'Data Complete'[Start of Hour]=VALUE("8:00:00 AM")),162,

                    if(and('Data Complete'[Station]="Core Table 4",'Data Complete'[Start of Hour]=VALUE("9:00:00 AM")),162,

                    if(and('Data Complete'[Station]="Core Table 4",'Data Complete'[Start of Hour]=VALUE("10:00:00 AM")),81,

                    if(and('Data Complete'[Station]="Core Table 4",'Data Complete'[Start of Hour]=VALUE("11:00:00 AM")),162,

                    if(and('Data Complete'[Station]="Core Table 4",'Data Complete'[Start of Hour]=VALUE("12:00:00 PM")),162,

                    if(and('Data Complete'[Station]="Core Table 4",'Data Complete'[Start of Hour]=VALUE("1:00:00 PM")),162,

                    if(and('Data Complete'[Station]="Core Table 4",'Data Complete'[Start of Hour]=VALUE("2:00:00 PM")),121,

                    if(and('Data Complete'[Station]="Core Table 4",'Data Complete'[Start of Hour]=VALUE("3:00:00 PM")),162,

                    if(and('Data Complete'[Station]="Core Table 4",'Data Complete'[Start of Hour]=VALUE("4:00:00 PM")),162,

                    if(and('Data Complete'[Station]="Core Table 4",'Data Complete'[Start of Hour]=VALUE("5:00:00 PM")),81,

                    if(and('Data Complete'[Station]="Core Table 4",'Data Complete'[Start of Hour]=VALUE("6:00:00 PM")),162,

                    if(and('Data Complete'[Station]="Core Table 4",'Data Complete'[Start of Hour]=VALUE("7:00:00 PM")),162,

                    if(and('Data Complete'[Station]="Core Table 4",'Data Complete'[Start of Hour]=VALUE("8:00:00 PM")),162,

                    if(and('Data Complete'[Station]="Core Table 4",'Data Complete'[Start of Hour]=VALUE("9:00:00 PM")),121,

                    if(and('Data Complete'[Station]="Core Table 4",'Data Complete'[Start of Hour]=VALUE("10:00:00 PM")),162,

                    if(and('Data Complete'[Station]="Core Table 4",'Data Complete'[Start of Hour]=VALUE("11:00:00 PM")),162,

                        if(and('Data Complete'[Station]="Core Table 5",'Data Complete'[Start of Hour]=VALUE("4:00:00 AM")),121,

                        if(and('Data Complete'[Station]="Core Table 5",'Data Complete'[Start of Hour]=VALUE("5:00:00 AM")),162,

                        if(and('Data Complete'[Station]="Core Table 5",'Data Complete'[Start of Hour]=VALUE("6:00:00 AM")),121,

                        if(and('Data Complete'[Station]="Core Table 5",'Data Complete'[Start of Hour]=VALUE("7:00:00 AM")),162,

                        if(and('Data Complete'[Station]="Core Table 5",'Data Complete'[Start of Hour]=VALUE("8:00:00 AM")),162,

                        if(and('Data Complete'[Station]="Core Table 5",'Data Complete'[Start of Hour]=VALUE("9:00:00 AM")),162,

                        if(and('Data Complete'[Station]="Core Table 5",'Data Complete'[Start of Hour]=VALUE("10:00:00 AM")),81,

                        if(and('Data Complete'[Station]="Core Table 5",'Data Complete'[Start of Hour]=VALUE("11:00:00 AM")),162,

                        if(and('Data Complete'[Station]="Core Table 5",'Data Complete'[Start of Hour]=VALUE("12:00:00 PM")),162,

                        if(and('Data Complete'[Station]="Core Table 5",'Data Complete'[Start of Hour]=VALUE("1:00:00 PM")),162,

                        if(and('Data Complete'[Station]="Core Table 5",'Data Complete'[Start of Hour]=VALUE("2:00:00 PM")),121,

                        if(and('Data Complete'[Station]="Core Table 5",'Data Complete'[Start of Hour]=VALUE("3:00:00 PM")),162,

                        if(and('Data Complete'[Station]="Core Table 5",'Data Complete'[Start of Hour]=VALUE("4:00:00 PM")),162,

                        if(and('Data Complete'[Station]="Core Table 5",'Data Complete'[Start of Hour]=VALUE("5:00:00 PM")),81,

                        if(and('Data Complete'[Station]="Core Table 5",'Data Complete'[Start of Hour]=VALUE("6:00:00 PM")),162,

                        if(and('Data Complete'[Station]="Core Table 5",'Data Complete'[Start of Hour]=VALUE("7:00:00 PM")),162,

                        if(and('Data Complete'[Station]="Core Table 5",'Data Complete'[Start of Hour]=VALUE("8:00:00 PM")),162,

                        if(and('Data Complete'[Station]="Core Table 5",'Data Complete'[Start of Hour]=VALUE("9:00:00 PM")),121,

                        if(and('Data Complete'[Station]="Core Table 5",'Data Complete'[Start of Hour]=VALUE("10:00:00 PM")),162,

                        if(and('Data Complete'[Station]="Core Table 5",'Data Complete'[Start of Hour]=VALUE("11:00:00 PM")),162                                        ))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

What I tried is shown above. The code works but it is limited to that number of stations. I need to add 5 additional stations to this report but when I add additional stations in the same format as the other stations within this code, Power BI says the query is too complex to fit into one query.

[lbendlin]

Core PPH =
IF (
    SEARCH ( [Station], "Core Table", 1, 0 ) = 1,
    SWITCH (
        TRUE (),
        [Start of Hour] IN { 4, 6, 14, 21 }, 121,
        [Sartt of Hour] IN { 10, 17 }, 81,
        162
    ),
    162
)

[Samuelroark01]

Thank you so much for the answer. The query ran great, however the hours that are supposed to change to either 121 or 81, are still showing 162. See picture below 

[lbendlin · Accepted solution]

Depends on what value type your [Start of Hour] is.  Might be enough to use HOUR([Start of Hour])