Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Mjolnir
Helper I
Helper I

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

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 ()
)

 

 

3 REPLIES 3
Mjolnir
Helper I
Helper I

Any ideas? Not managed to solve this yet.

jupsimarsingh
New Member

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!

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])
        )
    )

 

 



Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.