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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register 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
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

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! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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