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
TanFouc87
Frequent Visitor

Set default current year monthly values when options of slicer are not selected.

Hi guys, I have 2 tables - Date_ref and Asset. Date_ref is date table. Asset have monthly total asset value in which there are data from Jan 2021 - Aug 2023. I used a slider by year, if a year is selected, the monthly total asset values are displayed.

TanFouc87_0-1698057226137.png

 

When select 2023 in slider, it displays Jan-Aug

TanFouc87_1-1698057410597.png

I want to show data of current year (2023) as default when there is no tile in slicer selected, so i try writing the DAX below:

Total Asset =
VAR max_year = CALCULATE(MAX(Dates_ref[Year]))

VAR asset = CALCULATE(SUM(Asset[Value]), Asset[Attribute] = "Total Asset")

VAR default_asset = CALCULATE(SUM(Asset[Value]), Asset[Attribute] = "Total Asset", Dates_ref[Year] = max_year)

Return IF(NOT(ISFILTERED(Dates_ref)), default_asset, asset)

 

However the output is not as expected

TanFouc87_2-1698057701718.png

When I do not select any tile in slider, output display incorrect values, it also display 12 months while 2023 just have Jan - Aug.

How to show data of current year (2023) as default, Is there any problem with IF or ISFILTERED? 

Thank you in advance!

1 ACCEPTED SOLUTION
Walter_W2022
Resolver II
Resolver II

I would suggest using SELECTEDVALUE instead of ISFILTERED, could you please try below?

 

Total Asset =
VAR _selectedYear = SELECTEDVALUE(Dates_ref[Year])
VAR max_year = CALCULATE(MAX(Dates_ref[Year]))

VAR asset = CALCULATE(SUM(Asset[Value]), Asset[Attribute] = "Total Asset")

VAR default_asset = CALCULATE(SUM(Asset[Value]), Asset[Attribute] = "Total Asset", Dates_ref[Year] = max_year)

Return
IF(NOT(_selectedYear), default_asset, asset)

View solution in original post

2 REPLIES 2
Walter_W2022
Resolver II
Resolver II

I would suggest using SELECTEDVALUE instead of ISFILTERED, could you please try below?

 

Total Asset =
VAR _selectedYear = SELECTEDVALUE(Dates_ref[Year])
VAR max_year = CALCULATE(MAX(Dates_ref[Year]))

VAR asset = CALCULATE(SUM(Asset[Value]), Asset[Attribute] = "Total Asset")

VAR default_asset = CALCULATE(SUM(Asset[Value]), Asset[Attribute] = "Total Asset", Dates_ref[Year] = max_year)

Return
IF(NOT(_selectedYear), default_asset, asset)

It's worked, thank you so much.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 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.