Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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 ()
)
Any ideas? Not managed to solve this yet.
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])
)
)
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
9 | |
8 | |
7 | |
6 |
User | Count |
---|---|
14 | |
13 | |
11 | |
9 | |
8 |