topic Re: URL link in values not working on previous year's values - why? in DAX Commands and Tips

URL link in values not working on previous year's values - why?

Mjolnir — Fri, 17 May 2024 18:19:13 GMT

Hi,

A matrix displaying values from measures [expenses_current_year] and [expenses_previous_year]; where both measures are formatted to retrieve URL link to the related invoice, through the measure [URL link] below. It works properly with the measure [expenses_current_year] but fail to retrieve the URL link for values from [expenses_previous_year]. Years are stored as text in my data-sets. I tried to create a measure separately for previous year [URL link previous year expenses] but without any success.

Can someone please guide me on how to solve this matter?

IF ( ISFILTERED ( fact_table[transactionid] ), CONVERT ( CALCULATE ( MAX ( dim_link[URL] ), FILTER ( dim_orgdata, dim_orgdata[entity_id] = SELECTEDVALUE ( dim_orgdata[entity_id] ) ), FILTER ( dim_calendar, dim_calendar[year] = SELECTEDVALUE ( dim_calendar[year] ) || VALUE ( dim_calendar[year] ) = SELECTEDVALUE ( dim_calendar[year] ) - 1 ), FILTER ( fact_table, fact_table[transactionid] IN VALUES ( fact_table[transactionid] ) ) ), STRING ), BLANK () )

Re: URL link in values not working on previous year's values - why?

jupsimarsingh — Sun, 19 May 2024 01:03:28 GMT

Try creating Separate Measures for Each Year and ensure Correct Filter Context .Take a look at this :
Current Year Expenses URL =
IF (
ISFILTERED ( fact_table[transactionid] ),
CALCULATE (
MAX ( dim_link[URL] ),
FILTER (
dim_orgdata,
dim_orgdata[entity_id] = SELECTEDVALUE ( dim_orgdata[entity_id] )
),
FILTER (
dim_calendar,
dim_calendar[year] = SELECTEDVALUE ( dim_calendar[year] )
),
FILTER (
fact_table,
fact_table[transactionid] IN VALUES ( fact_table[transactionid] )
)
),
BLANK ()
)

For Previous Year Expenses URL :
Previous Year Expenses URL =
IF (
ISFILTERED ( fact_table[transactionid] ),
CALCULATE (
MAX ( dim_link[URL] ),
FILTER (
dim_orgdata,
dim_orgdata[entity_id] = SELECTEDVALUE ( dim_orgdata[entity_id] )
),
FILTER (
dim_calendar,
dim_calendar[year] = VALUE ( SELECTEDVALUE ( dim_calendar[year] ) ) - 1
),
FILTER (
fact_table,
fact_table[transactionid] IN VALUES ( fact_table[transactionid] )
)
),
BLANK ()
)

Please mark this as the solution if it works for you!

Re: URL link in values not working on previous year's values - why?

Mjolnir — Mon, 20 May 2024 07:37:25 GMT

Thank's for your effort, using a separate measure for previous year's URLs as you suggested now returns the URL string when the measure is being added to a column in a visual table.

Though, when conditionally formatting returned values in a matrix to contain the URL for previous year, it does not work.

I add the measure, "Actual amount previous year" to the value field in my matrixes, then under conditional formatting adding the URL, using the measure you suggest - no URL appearing in the values in the matrix. Though, your suggested measure returns the full URL string when added as a column to a visual table.

Could it be the measure for calculating previous year's actual amount that is causing the URL issue?

Actual amount previous year = CALCULATE ( SUM(fact_table[amount]), FILTER ( ALL(dim_calendar), VALUE(dim_calendar[year]) = SELECTEDVALUE(dim_calendar[year]) - 1 && dim_calendar[quarter] IN VALUES(dim_calendar[quarter]) && dim_calendar[month] IN VALUES(dim_calendar[month]) ) )

Re: URL link in values not working on previous year's values - why?

Mjolnir — Tue, 21 May 2024 07:37:57 GMT

Any ideas? Not managed to solve this yet.