cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
MWinter225 Member
Member

DAX for calendar filter controlling two tables

Hello all,

Currently I have a working report where I am controlling the displayed yearly data from two tables by a slicer on the page which is a year column slicer from the calendar. How I set up the report is that the user will select a single year and the data from the two tables filter on the page and it shows only the data for that year using the measure below:

Policies =
CALCULATE (
    DISTINCTCOUNT ( 'Table1'[Policy Number] ),
    FILTER (
        'Table1',
        MAX ( 'Calendar'[Year] ) = 'Table1'[Policy Effective Year]
    )
)

Everything works great but now they want a multiple year select and I can't for the life of me figure out the measure for that. So now I want them to be able to select multple years and have the measure return the distinct count of policies for all the years selected. I imagined that the DAX would look something like this but it isn't working:

Policies =
CALCULATE (
    DISTINCTCOUNT ( 'Table1'[Policy Number] ),
    FILTER (
        'Table1',
        ALLSELECTED( 'Calendar'[Year] ) IN {'Table1'[Policy Effective Year]}
    )
)

Any suggestions? I feel like this should be easy but its not. I can't just use a straight slicer of [Policy Effective Year] because I need that year slicer to control for both Table1 and Table2. No PBIX to share. Let me know if you have any more questions! Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
MWinter225 Member
Member

Re: DAX for calendar filter controlling two tables

I figured it out:

Policies =
VAR selectedYears =
    VALUES ( 'Calendar'[Year] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Table1'[Policy Number] ),
        FILTER (
            ALL ( 'Table1'[Policy Effective Year] ),
            'Table1'[Policy Effective Year] IN selectedYears
        )
    )

View solution in original post

1 REPLY 1
MWinter225 Member
Member

Re: DAX for calendar filter controlling two tables

I figured it out:

Policies =
VAR selectedYears =
    VALUES ( 'Calendar'[Year] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Table1'[Policy Number] ),
        FILTER (
            ALL ( 'Table1'[Policy Effective Year] ),
            'Table1'[Policy Effective Year] IN selectedYears
        )
    )

View solution in original post

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 55 members 963 guests
Please welcome our newest community members: