TechQA.

Need simplification on this DAX code in Power BI

24 views Asked by At

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,

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

                                            if(and('Data Complete'[Station]="Core Table 10",'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.

powerbi dax powerbi-desktop
Original Q&A
1

There are 1 answers

0
Olly On

You can simplify by checking the start of the Station string, then using a SWITCH function to choose a value based on the hour:

Core PPH = 
IF ( 
    LEFT ( 'Data Complete'[Station], 10 ) = "Core Table",
    SWITCH ( 
        HOUR ( 'Data Complete'[Start of Hour] ),
        4, 121,
        6, 121,
        10, 81,
        14, 121,
        17, 81,
        21, 121,
        162
    ),
    162
)

Related Questions in POWERBI

Related Questions in DAX

Related Questions in POWERBI-DESKTOP

Popular Questions

Popular Tags

javascript python java c# php android html jquery c++ css ios sql mysql r reactjs node.js arrays c asp.net json

Trending Questions