Do not show Years with no Data on Visual

[bdehning]

I have the followomng PBIX file.   https://drive.google.com/file/d/12jtqlGafWDMJSNIl1S8NtJuE8uB-MuJL/view?usp=drive_link

Can someone help take this farther and look at the _Include Measure and help fix it, so we not see all the blank years like below?   We just want to see the 3 lastest years only  For the Cust 2 only 2020, 2023 and 2024 should show on the X Axis.   

[danextian · Accepted solution]

Hi @bdehning 

Try this measure as  visual filter  and ensure that the X-axis is set to categorical.

Year Rank = 
RANKX (
    FILTER ( ALLSELECTED ( 'Date'[Year] ), NOT ( ISBLANK ( [Sales] ) ) ),
    CALCULATE ( MAX ( 'Date'[Year] ) ),
    ,
    DESC,
    DENSE
)

[bdehning]

Yes your measure worked on the sample report. 

Now I need to try to get that or a verison of that to work on my Dashboard Chart that is more complicated. 

I am trying to show only 3 years or 3 INCEPTIONDATE in this case.   When I apply your Year Rank I see this.  I use 3 measures to help provide the Top 3 Injury Cause for the last 3 INCEPTIONDATE Years.  You can see i have Injury Cause on X Axis and Count on Y Axis and Legenc provides the INCEPTIONDATE.  

[pankajnamekar25]

Hello @bdehning 

Use this measure on Visual level filter

Show Latest 3 Years =

VAR SelectedCust = SELECTEDVALUE('Customer'[Customer])

VAR AllYearsWithData =

    CALCULATETABLE(

        VALUES('Date'[Year]),

        NOT ISBLANK([Sales]),  // Replace [Sales] with your actual measure

        'Customer'[Customer] = SelectedCust

    )

VAR Top3Years =

    TOPN(3, AllYearsWithData, 'Date'[Year], DESC)

RETURN

    IF(MAX('Date'[Year]) IN Top3Years, 1, 0)

Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.

[bdehning]

Your measure says this when trying to save    A function 'PLACEHOLDER' has been used in a True/False expression that is used as a table filter expression. This is not allowed.  

[powerbidev123]

Hi @bdehning , in your visual level filter . Deselect the 2021 2022 2025 years. 

[bdehning]

The slicer on Cover is selecting the Customer.  So there will not be slicer on page 1.   

https://drive.google.com/file/d/104cWzIh7ei2zwQzFjXR1LkFAy6odeE7X/view?usp=drive_link