Forum Discussion

sid_5005's avatar
sid_5005
Frequent Visitor
2 years ago
Solved

Bar chart should show current year data when no value is selected in year slicer

Hi All,
I have a year slicer and the functionality that I am trying to achieve is the Bar chart should show current year data when no value is selected in year slicer. For this i created a measure as per the post below but its not working 
https://community.fabric.microsoft.com/t5/Desktop/Show-current-year-data-if-no-filter-is-selected/td-p/1120364 
Below is the code for the measure that I have created :
Measure = 

var selected_year = IF(ISFILTERED('Calendar'[Date]),SELECTEDVALUE('Calendar'[Date].[Year]),YEAR(TODAY()))
return
IF(SELECTEDVALUE('Calendar'[Date].[Year])=selected_year,1,0)
As  directed in the post when I apply this filter I am able to filter the bar chart to current year when no year is selected in the slicer but when I select a previous year I do not see the data for that year. Below are the screen shots :

Is there any other way to achieve this or does the DAX code needs to be tweaked ?
Thank you and Regards


 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi, sid_5005 

    May I ask if this is the expected output you are looking for? Based on your description, I have created many measures to achieve the effect you are looking for. Following picture shows the effect of the display.

     

    Measures:

    AppearDataByYear =

VAR _slicer_count =

    DISTINCTCOUNT ( 'Slicer'[Year] ) //Get all rows in the 'Slicer'[Year] column

VAR _all_count =

    COUNTROWS ( ALL ( 'Slicer' ) ) //Ignore the filtering of the Slicer table and calculate the number of rows in the Slicer table

VAR _selcet_years =

    ALLSELECTED ( 'Slicer'[Year] ) //Get the year filtered in the slicer

VAR _max_year =

    CALCULATE ( MAX ( 'Table'[Year] ), ALL () ) //Get the maximum value of the year in the 'Table'[Year] column, 2023

VAR _filter =

    SWITCH (

        TRUE (),

        _slicer_count = _all_count, IF ( SELECTEDVALUE ( 'Table'[Year] ) = _max_year, 1 ),

        //The slicer is not filtered, if the value in the 'Table'[Year] column is equal to 2023, give a status 1

        _slicer_count < _all_count,

            IF ( SELECTEDVALUE ( 'Table'[Year] ) IN _selcet_years, 1 ) //The slicer is partially filtered, if the range of values in column 'Table'[Year] is in _selcet_years, give a status 1

    )

RETURN

    _filter

     

    If this does not work, could you please share some sample data without sensitive information and expected output.
    How to provide sample data in the Power BI Forum - Microsoft Fabric Community

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi, sid_5005 

    May I ask if this is the expected output you are looking for? Based on your description, I have created many measures to achieve the effect you are looking for. Following picture shows the effect of the display.

     

    Measures:

    AppearDataByYear =

VAR _slicer_count =

    DISTINCTCOUNT ( 'Slicer'[Year] ) //Get all rows in the 'Slicer'[Year] column

VAR _all_count =

    COUNTROWS ( ALL ( 'Slicer' ) ) //Ignore the filtering of the Slicer table and calculate the number of rows in the Slicer table

VAR _selcet_years =

    ALLSELECTED ( 'Slicer'[Year] ) //Get the year filtered in the slicer

VAR _max_year =

    CALCULATE ( MAX ( 'Table'[Year] ), ALL () ) //Get the maximum value of the year in the 'Table'[Year] column, 2023

VAR _filter =

    SWITCH (

        TRUE (),

        _slicer_count = _all_count, IF ( SELECTEDVALUE ( 'Table'[Year] ) = _max_year, 1 ),

        //The slicer is not filtered, if the value in the 'Table'[Year] column is equal to 2023, give a status 1

        _slicer_count < _all_count,

            IF ( SELECTEDVALUE ( 'Table'[Year] ) IN _selcet_years, 1 ) //The slicer is partially filtered, if the range of values in column 'Table'[Year] is in _selcet_years, give a status 1

    )

RETURN

    _filter

     

    If this does not work, could you please share some sample data without sensitive information and expected output.
    How to provide sample data in the Power BI Forum - Microsoft Fabric Community

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum