Forum Discussion

fraz's avatar
fraz
Regular Visitor
3 years ago

Optimising measure to replace URL column

I have a dataset with a table containing barcodes, and I have been generating a URL from these with a simple concatenation in SQL prior to import by my Power BI dataset. e.g.:

'https://first-part-of-url.com/'||"Barcode"

That links to the item's page on my company's internal system. It's mainly used in tables within drill-through or detail pages in my reports. All my tables have been imported, no DQ. 

 

I was looking at the size of my dataset and saw that each URL was taking about 10 - 15% of my dataset size. That makes sense given the number of unique values of that field. So I thought I'd use a measure that could be used in those tables. I was aware I'd be reducing size/load speed at the expense of load times within the report.

 

Here's the measure I drafted:

Item URL = IF(
    HASONEVALUE('Item Summary'[Barcode]),
    "https://first-part-of-url.com/" & VALUES('Item Summary'[Barcode])
)

I did some benchmarking on DAX studio and on a test table query the duration of the query went from 67 ms to 1365 ms. That jump in load time is higher than I was expecting. 

 

Is there a more efficient DAX expression to replace this column with a measure? 

 

Thank you!

COLUMN VALUES
Optimizing DAX

2 Replies

  • johnt75's avatar
    johnt75
    Super User
    3 years ago

    Not sure how much difference it would make but you could try replacing VALUES with SELECTEDVALUE.

    • fraz's avatar
      fraz
      Regular Visitor
      3 years ago

      I changed SELECTEDVALUE to VALUES when I entered the HASONEVALUE condition. I think it would only speed things up if I removed that IF condition to just make it

      Item URL = "https://first-part-of-url.com/" & SELECTEDVALUE('Item Summary'[Barcode]

      But then I'd end up with the URL missing a Barcode if it was in a filter context with more than one Barcode i.e. "https://first-part-of-url.com/"

      ====

      Update - I tested using SELECTEDVALUE:

      Item URL = IF(
          HASONEVALUE('Item Summary'[Barcode]),
          "https://first-part-of-url.com/" & SELECTEDVALUE('Item Summary'[Barcode])
      )

      That takes me up to 1900 ms. So that's not the one. 

       

      Appreciate the suggestion though!