Search code examples
powerbidaxmeasureslicers

How to make value of Y-axis blank based on selected value in slicer that refer to same column that X-axis also refer to?


I have an issue I wish I would get any advice from you. I have 2 table that relates each other which are fact_table and calendar_table. I would create a simple column chart where MonthName (from calendar) as X-Axis and ValueColumn (from fact_table) as Y-axis. Then I create a slicer that refer to MonthName column (same as X-Axis). I expect when I select Slicer (example, April), then the chart will display values of Jan-April and leave May-Dec blank (with X axis values fixed; Jan-Dec, eventho blank).

Would you please assist me what DAX measure I should create to fulfill above requirement. Thank you

sample data as follows where both relates to each other through column 'Date'. And for both slicer and X axis refer to same column: Calendar[MonthName].

Table Calendar

Date MonthName MonthNumber Year
1-1-2022 January 1 2022
1-2-2022 February 2 2022

Table Fact

Date Category Value
1-1-2022 Actual 1000
1-2-2022 Estimate 1200

Solution

  • You can try a measure as below-

    measure = 
    
    var max_date = Max(Calendar[Date])
    var max_year = Max(Year(Calendar[Date]))
    
    RETURN
    SWITCH(
        ISFILTERED (Calendar[Date]),
        TRUE(),
        CALCULATE(
            SUM(fact_table[column_name]),
            FILTER(
                ALL(Calendar),
                Year(Calendar[Date]) = max_year
                    && Calendar[Date] <= max_date
            )
        ),
        SUM(fact_table[column_name])
    )