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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
IvanS
Helper V
Helper V

Date slicer not working properly with Matrix (table filter working fine)

Hi guys,

 

I have table with activities done/not done for our projects with column "Done" which contains values Yes/No. I have created matrix visual as per attached screenshot. There was request from business to populate value "n/a" for those activities that are not part of project - therefore I have created following measure.

 

Measure_Done = 
IF(MAX(RealizationFinalCheck[Done]) = "Yes", "Yes",
    IF(MAX(RealizationFinalCheck[Done]) = "No", "No",
    "n/a"
    )
)

 

If there is no date selected, measure is properly replacing blank values with "n/a". The problem starts when the date is selected (e.g. 2023). As shown on second screen, year in filter Date of "InOperation" is selected to 2023 however the projects without Date of "InOperation" are still visible and the Yes/No values are all replaced by "n/a". The replacement is due to measure - this is clear to me. 

 

IvanS_0-1693828343591.png

IvanS_1-1693828936683.png

However, if I select date filter in the filter panel on table visual and select year 2023, the table is filterred properly and projects without date of "InOperation" are removed from matrix.

IvanS_2-1693829110821.png

 

Can you please guide me to the right direction how to enable filterring matrix via slicer?

 

Thank you
IvanS

1 ACCEPTED SOLUTION
v-rongtiep-msft
Community Support
Community Support

Hi @IvanS ,

Please have a try.

Measure_Done =
IF (
    HASONEVALUE ( 'Date'[Year] ),
    VAR SelectedYear =
        SELECTEDVALUE ( 'Date'[Year] )
    RETURN
        IF (
            MAX ( RealizationFinalCheck[Done] ) = "Yes"
                && MAX ( RealizationFinalCheck[Date of "InOperation"] ) = SelectedYear,
            "Yes",
            IF (
                MAX ( RealizationFinalCheck[Done] ) = "No"
                    && MAX ( RealizationFinalCheck[Date of "InOperation"] ) = SelectedYear,
                "No",
                "n/a"
            )
        ),
    BLANK ()
)

 

How to Get Your Question Answered Quickly 

 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

 

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

 

 

View solution in original post

3 REPLIES 3
v-rongtiep-msft
Community Support
Community Support

Hi @IvanS ,

Please have a try.

Measure_Done =
IF (
    HASONEVALUE ( 'Date'[Year] ),
    VAR SelectedYear =
        SELECTEDVALUE ( 'Date'[Year] )
    RETURN
        IF (
            MAX ( RealizationFinalCheck[Done] ) = "Yes"
                && MAX ( RealizationFinalCheck[Date of "InOperation"] ) = SelectedYear,
            "Yes",
            IF (
                MAX ( RealizationFinalCheck[Done] ) = "No"
                    && MAX ( RealizationFinalCheck[Date of "InOperation"] ) = SelectedYear,
                "No",
                "n/a"
            )
        ),
    BLANK ()
)

 

How to Get Your Question Answered Quickly 

 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

 

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

 

 

amitchandak
Super User
Super User

0 between range
Measure = var _1= if(IF(MAX(RealizationFinalCheck[Done]) = "Yes", "Yes",
IF(MAX(RealizationFinalCheck[Done]) = "No", "No",
"n/a"
)
))
var _year = maxx(ALLSELECTED('Calendar'), 'Calendar'[Year])
return
if(max('Calendar'[Year]) =_year , BLANK(), _1)

Hi @amitchandak ,
I am having error in that function. Don't you miss "result if true" part?

IvanS_0-1693838148756.png

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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