Search code examples
powerbidaxtableau-apipowerbi-desktop

Power BI multiple if contains split converting from Tableau calculated measure

I am a new Power BI user switching from Tableau rather suddenly, but I'm having a little trouble with a DAX expression.

I have a column (Detail1) of URLs, some of which (ending in .aspx, .ppt, .pdf, .doc) have some garbage strings appended to them. In tableau I created a calculated column from the following calculation to clean them up:

    IF CONTAINS([Detail1],".aspx") THEN
        SPLIT([Detail1], ".aspx",1) + ".aspx"
    ELSEIF CONTAINS([Detail1],".ppt") THEN
        SPLIT([Detail1], ".ppt",1) + ".ppt"
    ELSEIF CONTAINS([Detail1],".pdf") THEN
        SPLIT([Detail1], ".pdf",1) + ".pdf"
    ELSEIF CONTAINS([Detail1],".doc") THEN
        SPLIT([Detail1], ".doc",1) + ".doc"
    ELSE
        [Detail1]
    END

However I'm struggling with doing this in a DAX expression. Could anyone point me in the right direction?

Your help is greatly appreciated!

Solution

  • I have created a dummy table containing the URL with undesired extra information. I have two DAX solutions to your problem.

    Solution 1: DAX

    Table

    +--------------------------------------+
|                Detail1               |
+--------------------------------------+
| https://url.com/page1.aspx?extrainfo |
+--------------------------------------+
| https://url.com/page1.doc?extrainfo  |
+--------------------------------------+
| https://url.com/page1.ppt?extrainfo  |
+--------------------------------------+
| https://url.com/page1.pdf?extrainfo  |
+--------------------------------------+
| https://url.com/page1.doc?extrainfo  |
+--------------------------------------+

    Without knowing your data, the easiest solution is to extract everything before the ? sign, I can imagine that maybe you have a similar structure in your dataset.

    If that's is the case you can create the following calculated column

    CleanUrl = 
PATHITEMREVERSE(SUBSTITUTE([Detail1],"?","|"),2,TEXT)

    Creating this table

    +--------------------------------------+----------------------------+
|                Detail1               |          CleanUrl          |
+--------------------------------------+----------------------------+
| https://url.com/page1.aspx?extrainfo | https://url.com/page1.aspx |
+--------------------------------------+----------------------------+
| https://url.com/page1.doc?extrainfo  | https://url.com/page1.doc  |
+--------------------------------------+----------------------------+
| https://url.com/page1.ppt?extrainfo  | https://url.com/page1.ppt  |
+--------------------------------------+----------------------------+
| https://url.com/page1.pdf?extrainfo  | https://url.com/page1.pdf  |
+--------------------------------------+----------------------------+
| https://url.com/page1.doc?extrainfo  | https://url.com/page1.doc  |
+--------------------------------------+----------------------------+

    Solution 2: DAX

    If you don't have a clear pattern in your column Detail1, the following calculated columns take your same logic and adapts it to DAX.

    CleanUrlV2 =
    SWITCH (
    TRUE (),
    CONTAINSSTRING ( [Detail1], ".aspx" ), PATHITEMREVERSE ( SUBSTITUTE ( [Detail1], ".aspx", "|" ), 2, TEXT ) & ".aspx",
    CONTAINSSTRING ( [Detail1], ".ppt" ), PATHITEMREVERSE ( SUBSTITUTE ( [Detail1], ".ppt", "|" ), 2, TEXT ) & ".ppt",
    CONTAINSSTRING ( [Detail1], ".pdf" ), PATHITEMREVERSE ( SUBSTITUTE ( [Detail1], ".pdf", "|" ), 2, TEXT ) & ".pdf",
    CONTAINSSTRING ( [Detail1], ".doc" ), PATHITEMREVERSE ( SUBSTITUTE ( [Detail1], ".doc", "|" ), 2, TEXT ) & ".doc",
    [Detail1]
)

    Output Table

    +--------------------------------------+----------------------------+
|                Detail1               |         CleanUrlV2         |
+--------------------------------------+----------------------------+
| https://url.com/page1.aspx?extrainfo | https://url.com/page1.aspx |
+--------------------------------------+----------------------------+
| https://url.com/page1.doc?extrainfo  | https://url.com/page1.doc  |
+--------------------------------------+----------------------------+
| https://url.com/page1.ppt?extrainfo  | https://url.com/page1.ppt  |
+--------------------------------------+----------------------------+
| https://url.com/page1.pdf?extrainfo  | https://url.com/page1.pdf  |
+--------------------------------------+----------------------------+
| https://url.com/page1.doc?extrainfo  | https://url.com/page1.doc  |
+--------------------------------------+----------------------------+

    For some reason Stackoverflow doesn't support Markdown tables with //.