Hello there,
I am looking for the right formula to create nested if-thens (am not sure if the formula should be if-then, but that is what i know from excel).
I want assign categories to vessels, and this is based on a main shiptype (the first IF), a sub shiptype (the second IF) and a vessel size (third IF).
Could someone help me or guide me in the right direction what DAX formula to use?
Many thanks in advance
Rather than using complex nested IF statements, I would strongly recommend using the SWITCH function instead. Much easier to write, debug, and understand than multilevel IF statements. Here are a couple of videos that walk through how to use SWITCH to address issues similar to the one you described above:
https://forum.enterprisedna.co/t/using-switch-true-logic-in-power-bi-dax-concepts/488
https://forum.enterprisedna.co/t/utilizing-switch-true-formula-techniques/3446
Hope this is helpful.
Hi,
Not sure what your intention is, lacking a data model or example pbix. Are you creating a measure using SWITCH, which in itself is the correct solution for nested IF’s, or building a table (like a Products table) with a calculated column?
Paul
Good morning, many thanks for the quick reply, i will have a look at the video.
Good morning Paul, Thanks for the reply.
I will try to elaborate a bit more about what i am aiming for. I have an example of it made in Crystal reports (see Word attachment) Defining main shiptype.docx (13.7 KB)
This is what i am trying to achieve in PBI. The basic dataset is access, which is updated each month. I would like to assign vessel types based on multiple fields from the data set.
I am not sure if i should use a measure or a custom column ?
Hope you can help me again on this.
Regards,
Jeffrey
Hi,
Wow, quite a list.
This is something that I would do in Power Query, are you familiar with PQ?
query 1 = create a list (Excel, Access) with the types (which you can maintain with any changes in the future)
query 2 = your shipping data.
Merging the two queries will create the “lookup” for you by record for the straight forward main ship types, this is the easy part.
For the TEU and other classifications, I would have a go at adding conditional columns in query 2 (in the query editor) and/or column by example.
If that does not work for you it gets more complicated, involving both merging and appending, you need another query that you create from the possible classification combinations.
then append
query 2 =your shipping data
query 3 = TEU classification with the TEU classification query,
This is a special technique that I learned from Oz du Soleil, he publishes very good YT video’s on PQ.
Hope this will get you going, you will need a combination of the above techniques to achieve your goal. Execution is outside the scope of this forum and would be more of a consultancy type of service.
Paul
Hi,
Did you succeed with this?
Paul
Dear Paul, have looked at the solution, but lack the time right now to implement. Will keep you posted when i pick this up again. Regards, Jeffrey
Hi,
ok, good luck with it. It will take some effort to set up correctly but you will gain the benefits after.
Paul
Hi there Paul, i finally managed to get the formulas in. in a 3 step appraoch, with the help of “if-thens”, but… it works !
That is great. I know how it feels when you get results in more complex situations.
Paul
