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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.