Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Not sure how much difference it would make but you could try replacing VALUES with SELECTEDVALUE.
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!
User | Count |
---|---|
42 | |
28 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |