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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
VenkatTav
Frequent Visitor

Slicer not working properly on my Measure

Hi All,

I have created a measure and building a simple report with Recorded Date and measure as columns, I'm using Year(RecordedDate) as slicer, When i select a year in slicer my report is showing zero's for all the year and giving values only for slicer selected Year,

CNT_LNBR_CH07 =
VAR CNT = CALCULATE(COUNT(LOAN[LOAN_NBR]),LOAN[CHAPTER_NBR]=7)
RETURN IF(ISBLANK(CNT),0,CNT)
 
Reference screenshot : I selected only 1990 in slicer but it is giving all the years data with zero's which is wrong , As per requirement when i select 1990 only 1990 data should be displayed in the report.
VenkatTav_0-1673858308315.png

Thanks in Advance.

3 REPLIES 3
Samarth_18
Community Champion
Community Champion

Hi @VenkatTav ,

 

You will get 0 since your code added to 0 if its blank, you can update your code as below:-

 

CNT_LNBR_CH07 = CALCULATE(COUNT(LOAN[LOAN_NBR]),LOAN[CHAPTER_NBR]=7)

 

For further assistance please help with below details:-

Could you please share your sample data in raw format not screenshot?

Please let us know your date also coming from Loan table?

  

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin

You can not display blank values in reports , you have to convert blanks or nulls to 0's in reports, it will be usual case in all the organizations.

 

Yes Recorded date is coming from Loan table only 

Consider this as sample data.

Loan Recorded dateLoan NBRChapter
01-01-19881234517
02-01-19881345627
03-01-19881234537
04-01-198812345411
05-01-199212345811
05-01-199212345911
05-01-199212345613
05-01-199212345713
09-01-199312345813
10-01-199312349613

 

@VenkatTav , You could create a another measure as below and use it as filter on visual:-

 

_filter =
IF (
    YEAR ( MAX ( Loan[RecordedDate] ) )
        = YEAR ( SELECTEDVALUE ( Loan[RecordedDate] ) )
        && NOT ( ISBLANK ( MAX ( Loan[RecordedDate] ) ) ),
    1,
    0
)

image.png

 

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.