How to simplify a long formula like this?

dsiffredi · February 29, 2020, 8:20pm

Hello, I’m basically calculating a column based on a string that can have a small variance , thus the use of OR statement. the issue I have is that this formula might grow overtime and I want to make sure I can simplify it with perhaps the use of a variable or nested statements , something that could make this future proof or at least less convoluted.

here’s a little sample and thanks btw!

POD# = 
SWITCH(TRUE(),
    CALCULATE(OR(CONTAINSSTRING(SELECTEDVALUE(PODs[POD]),"opod 01"),(CONTAINSSTRING(SELECTEDVALUE(PODs[POD]),"opod01")))),"OPOD 01",
    CALCULATE(OR(CONTAINSSTRING(SELECTEDVALUE(PODs[POD]),"opod 2"),(CONTAINSSTRING(SELECTEDVALUE(PODs[POD]),"opod2")))),"OPOD 02",
    CALCULATE(OR(CONTAINSSTRING(SELECTEDVALUE(PODs[POD]),"opod 3"),(CONTAINSSTRING(SELECTEDVALUE(PODs[POD]),"opod3")))),"OPOD 03",
    CALCULATE(OR(CONTAINSSTRING(SELECTEDVALUE(PODs[POD]),"opod 4"),(CONTAINSSTRING(SELECTEDVALUE(PODs[POD]),"opod4")))),"OPOD 04",
    CALCULATE(OR(CONTAINSSTRING(SELECTEDVALUE(PODs[POD]),"opod 5"),(CONTAINSSTRING(SELECTEDVALUE(PODs[POD]),"opod5")))),"OPOD 05",
    CALCULATE(OR(CONTAINSSTRING(SELECTEDVALUE(PODs[POD]),"opod 6"),(CONTAINSSTRING(SELECTEDVALUE(PODs[POD]),"opod6")))),"OPOD 06",
    CALCULATE(OR(CONTAINSSTRING(SELECTEDVALUE(PODs[POD]),"opod 7"),(CONTAINSSTRING(SELECTEDVALUE(PODs[POD]),"opod7")))),"OPOD 07",
    CALCULATE(OR(CONTAINSSTRING(SELECTEDVALUE(PODs[POD]),"opod 8"),(CONTAINSSTRING(SELECTEDVALUE(PODs[POD]),"opod8")))),"OPOD 08",
    CALCULATE(OR(CONTAINSSTRING(SELECTEDVALUE(PODs[POD]),"opod 9"),(CONTAINSSTRING(SELECTEDVALUE(PODs[POD]),"opod9")))),"OPOD 09",
    CALCULATE(OR(CONTAINSSTRING(SELECTEDVALUE(PODs[POD]),"opod 10"),(CONTAINSSTRING(SELECTEDVALUE(PODs[POD]),"opod10")))),"OPOD 10",
    CALCULATE(OR(CONTAINSSTRING(SELECTEDVALUE(PODs[POD]),"opod 11"),(CONTAINSSTRING(SELECTEDVALUE(PODs[POD]),"opod11")))),"OPOD 11",
    CALCULATE(OR(CONTAINSSTRING(SELECTEDVALUE(PODs[POD]),"opod 12"),(CONTAINSSTRING(SELECTEDVALUE(PODs[POD]),"opod12")))),"OPOD 12",
    CALCULATE(OR(CONTAINSSTRING(SELECTEDVALUE(PODs[POD]),"opod 13"),(CONTAINSSTRING(SELECTEDVALUE(PODs[POD]),"opod13")))),"OPOD 13",
    CALCULATE(OR(CONTAINSSTRING(SELECTEDVALUE(PODs[POD]),"opod 14"),(CONTAINSSTRING(SELECTEDVALUE(PODs[POD]),"opod14")))),"OPOD 14",
    CALCULATE(OR(CONTAINSSTRING(SELECTEDVALUE(PODs[POD]),"opod 15"),(CONTAINSSTRING(SELECTEDVALUE(PODs[POD]),"opod15")))),"OPOD 15",
    CALCULATE(OR(CONTAINSSTRING(SELECTEDVALUE(PODs[POD]),"opod 16"),(CONTAINSSTRING(SELECTEDVALUE(PODs[POD]),"opod16")))),"OPOD 16",
    CALCULATE(OR(CONTAINSSTRING(SELECTEDVALUE(PODs[POD]),"opod 17"),(CONTAINSSTRING(SELECTEDVALUE(PODs[POD]),"opod17")))),"OPOD 17",
    CALCULATE(OR(CONTAINSSTRING(SELECTEDVALUE(PODs[POD]),"opod 18"),(CONTAINSSTRING(SELECTEDVALUE(PODs[POD]),"opod18")))),"OPOD 18",
    CALCULATE(OR(CONTAINSSTRING(SELECTEDVALUE(PODs[POD]),"opod 19"),(CONTAINSSTRING(SELECTEDVALUE(PODs[POD]),"opod19")))),"OPOD 19",
    CALCULATE(OR(CONTAINSSTRING(SELECTEDVALUE(PODs[POD]),"opod 20"),(CONTAINSSTRING(SELECTEDVALUE(PODs[POD]),"opod20")))),"OPOD 20",
    CALCULATE(OR(CONTAINSSTRING(SELECTEDVALUE(PODs[POD]),"mpod 1"),(CONTAINSSTRING(SELECTEDVALUE(PODs[POD]),"mpod1")))),"MPOD 01",
    CALCULATE(OR(CONTAINSSTRING(SELECTEDVALUE(PODs[POD]),"mpod 2"),(CONTAINSSTRING(SELECTEDVALUE(PODs[POD]),"mpod2")))),"MPOD 02",
    CALCULATE(OR(CONTAINSSTRING(SELECTEDVALUE(PODs[POD]),"mpod 3"),(CONTAINSSTRING(SELECTEDVALUE(PODs[POD]),"mpod3")))),"MPOD 03",
    CALCULATE(OR(CONTAINSSTRING(SELECTEDVALUE(PODs[POD]),"mpod 4"),(CONTAINSSTRING(SELECTEDVALUE(PODs[POD]),"mpod4")))),"MPOD 04",
    CALCULATE(OR(CONTAINSSTRING(SELECTEDVALUE(PODs[POD]),"mpod 5"),(CONTAINSSTRING(SELECTEDVALUE(PODs[POD]),"mpod5")))),"MPOD 05",
    CALCULATE(OR(CONTAINSSTRING(SELECTEDVALUE(PODs[POD]),"mpod 6"),(CONTAINSSTRING(SELECTEDVALUE(PODs[POD]),"mpod6")))),"MPOD 06",
    CALCULATE(OR(CONTAINSSTRING(SELECTEDVALUE(PODs[POD]),"mpod 7"),(CONTAINSSTRING(SELECTEDVALUE(PODs[POD]),"mpod7")))),"MPOD 07",
    CALCULATE(OR(CONTAINSSTRING(SELECTEDVALUE(PODs[POD]),"mpod 8"),(CONTAINSSTRING(SELECTEDVALUE(PODs[POD]),"mpod8")))),"MPOD 08",
    CALCULATE(OR(CONTAINSSTRING(SELECTEDVALUE(PODs[POD]),"mpod 9"),(CONTAINSSTRING(SELECTEDVALUE(PODs[POD]),"mpod9")))),"MPOD 09",
    CALCULATE(OR(CONTAINSSTRING(SELECTEDVALUE(PODs[POD]),"mpod 10"),(CONTAINSSTRING(SELECTEDVALUE(PODs[POD]),"mpod10")))),"MPOD 10",
    "Other")

BrianJ · February 29, 2020, 8:50pm

@dsiffredi,

Welcome to the forum!

Have you considered doing this in Power Query, rather than DAX? This is really a data prep issue, which Power Query will make short work of, with the added benefit of your report running much faster once the data is loaded not having to execute that long SWITCH(TRUE)) statement.

Brian

hafizsultan · February 29, 2020, 9:03pm

Hi @dsiffredi,

Welcome to the forum. As Brian said, it is much better to do it in Power query. However, you can make use of a helping table/mapping table which contains all the required information like below. I have created sample table of current & required values. So, it will check current value and provide you the required value as your long query is doing.

Sample Table:

DAX:

OPD# =

VAR selectedvalue =
    SELECTEDVALUE ( 'Test'[Current], "Other" )
RETURN
    CALCULATE ( VALUES ( 'Test'[Required] ), 'Test'[Current] IN { selectedvalue } )

Output:

eDNA_SelectedValue.pbix (36.0 KB) g)

Sample file is attached.

BrianJ · February 29, 2020, 10:06pm

@dsiffredi,

If you going to do it via DAX, @hafizsultan’s approach is really nice way to simplify.

I put together a mockup example based on your initial post, showing how easy this in PQ. Once your data is loaded, four simple steps all done straight through the UI, and from that point forward it will just run automatically upon load or refresh, regardless of how many more pods you append to the original list.

I’ve attached the solution file if you want to browse through the details of each step via the “gear” icon next to each one.

Brian
eDNA Forum - Simplify Formula Solution.pbix (21.7 KB)

dsiffredi · March 1, 2020, 10:49pm

thank you so much, I tested the version of doing this by PQ and it won’t work because the table is a table created by summarizecolumns function, so it’s not accessible by PQ. :-s

any other ideas?

dsiffredi · March 1, 2020, 11:08pm

Close but not there yet. thanks so much for taking the time.
what I’m looking at is to create a calculated column (not a measure) .This method is quite good but seems to be working only for measures. How can I make this to work to create a calculated column ?
BTW: PQ won’t work on this scenario because the table is the result of a summarizecolumns function so PQ doesn’t see it.

hafizsultan · March 1, 2020, 11:22pm

Hi @dsiffredi,

Oh, so you are unable to use PQ as it would be much simpler. As a matter of fact, creating required calculated column is even easier option that creating measure if you have mapping table. Please find attached sample pbix file. “Required” is calculated column.

eDNA_SelectedValue.pbix (41.7 KB)

EnterpriseDNA · March 3, 2020, 11:02pm

Hi @dsiffredi, we’ve noticed that no response has been received from you since the 2nd of March. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

dsiffredi · March 10, 2020, 8:45pm

no solution was found , but I took a different approach to it and shortened the formula by half so I’m okay with it.