Support with simplifying my DAX code

[Anonymous]

Hi all, 

I was hoping you may be able to help me, I wrote some DAX a while back for VLOOKUPs and I'm hoping to simplyfy it. I've given it a go today but I am unsure what I'm missing (its probably very small!). The DAX I created in the past is in 8 parts that I would like to bring down to just one DAX formula. The 8 parts, that are seperate columns, are below:

Part1 = IF(AND(OR(LEFT('Data1'[ID],3)="SEG", LEFT('Data1'[ID],4,)="DTFA"),RIGHT('Data1'[ID],3)="001")), "001","")

Part2 = IF(AND(OR(LEFT('Data1'[ID],3)="SEG", LEFT('Data1'[ID],4,)="DTFA"),RIGHT('Data1'[ID],3)="002")), "002","")

Part3 = IF(AND(OR(LEFT('Data1'[ID],3)="SEG", LEFT('Data1'[ID],4,)="DTFA"),RIGHT('Data1'[ID],3)="003")), "003","")

Part4 = IF(AND(OR(LEFT('Data1'[ID],3)="SEG", LEFT('Data1'[ID],4,)="DTFA"),RIGHT('Data1'[ID],3)="004")), "004","")

Part5 = IF(AND(OR(LEFT('Data1'[ID],3)="SEG", LEFT('Data1'[ID],4,)="DTFA"),RIGHT('Data1'[ID],3)="005")), "005","")

Part6 = IF(LEFT('Data1'[ID],1)="G", "G", "")

Part7 = IF(LEFT('Data1'[ID],4)="WB90", "WB90", "")

Part 8 = 'Data1'[Part1]&'Data1'[Part2]&'Data1'[Part3]&'Data1'[Part4]&'Data1'[Part5]&;Data1'[Part6]&'Data1'[Part7]

Please don't judge the clunckyness of the above as I was new to DAX and also on a tight deadline to get results

So I've had a long hard thought about how to make this easier, it will still need to be created as a column as I plan on using it as a filter. So, to start with I created two new tables (called 'Table(1)' and 'Table (2)). The first table lists all the letter parts above (SEG, WB90 etc) and table 2 lists all the number parts above (001,002,003 etc.). In table two I also have another column called 'Results' that basically lists what I would like the result to be from all this. 

Secondly I created a relationship between Table1 & Data1 and Table2 & Data 1. These were both Many to One, Single to match against the [ID] column (but really they don't match as the two smaller tables are partial codes). I then created the below formula:

New Part = IF(OR('Data1'[ID]='Table(1)'[Col1], (RIGHT('Data1'[ID]='Table (2)'[Col2]))), 'Table (2)'[Result],"")

However I get the following error:

"A single value for column 'Result' in table 'Table (2)' cannont be determined This can happwen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result"

That's weird because the 'Results' column are all unique values (just counting up to 5 actually). 

Any help would be great, thank you! 

E

[MattAllington · Accepted solution]

I can almost guarantee this is not the best approach. Your formulas are using runtime (measures are runtime) text searches to segment your results. It is better to pre-segment the data in one or more columns in your tables, using power query (preferably) or calc columns. I suggest you investigate a conditional column in power query and see if you can generate a new single column that is loaded into the model that already has these new classifications. 

[Anonymous]

EDIT:

I found another thread where you had helped someone else with the same issue here: https://community.powerbi.com/t5/Power-Query/Expression-Error-We-cannot-convert-the-value-null-to-ty...

I followed this advice by going back to my previous steps and checking for errors. I have now fixed this issue. 

Thank you!

E

Hello,

I did as you suggested and I went back to Power Query. I created two conditional columns to search the ID column to begin with. This leaves me with columns that have values SEG, DTFA, G, WB90 and then 001, 002, 003, 004, 005 etc. Any columns that don't match either of these I just have 0 in that column. 

I then created a third conditional column to bring these together and put the following formula in:

= if [Custom1]="SEG" or "DTFA" and [Custom2]="001" then "001" else if [Custom1]="SEG" or "DTFA" and [Custom2]="002" then "002" else if [Custom1]="SEG" or "DTFA" and [Custom2]="003" then "003" else if [Custom1]="SEG" or "DTFA" and [Custom2]="004" then "004" else if [Custom1]="SEG" or "DTFA" and [Custom2]="005" then "005" else if [Custom1]="G" then "G" else if [Custom1]="WB90" then "WB90" else ""

There is no syntax error however when I go to load that into my table I get the following error:

"We can not convert the value "DTFA" to type logical"

I went back to a step and formatted both custom columns to type text to fix this however no such luck.

Thanks,

E

[MattAllington]

well the error suggests the column is being treated as logical in Power BI. I suggest you remove that column as the last step in PQ, then load. Then go back and remove this last step, and load again. See how that goes.