Forum Discussion

Rjesak's avatar
Rjesak
Helper I
5 years ago
Solved

filter OR with multiple options

I have a table that has receipt_date and closure_date and those are also broken into calendar years so I also have receipt_CY and closure_CY.   I created a measure for the current year - just YEAR(...
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    Hi, Rjesak 

    Please check the below picture and the sample pbix file's link down below.

    Instead of having extra columns for a year in the fact table, try having a dim-calendar table like the below, and create a measure like below.

     

     

    Count Unique Seq =
    IF (
    ISFILTERED ( 'Calendar' ),
    CALCULATE (
    COUNTROWS ( data ),
    FILTER (
    data,
    AND (
    data[receipt_date] <= MAX ( 'Calendar'[Date] ),
    OR (
    data[closure_date] >= MIN ( 'Calendar'[Date] ),
    data[closure_date] = BLANK ()
    )
    )
    )
    )
    )

     

    https://www.dropbox.com/s/d26henlpvp41c87/rjesakv2.pbix?dl=0 

     

     

    Hi, My name is Jihwan Kim.

     

    If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

     

    Linkedin: https://www.linkedin.com/in/jihwankim1975/

Jihwan_Kim's avatar
Jihwan_Kim
Super User
5 years ago

Hi, Rjesak 

Please check the below picture and the sample pbix file's link down below.

Instead of having extra columns for a year in the fact table, try having a dim-calendar table like the below, and create a measure like below.

 

 

Count Unique Seq =
IF (
ISFILTERED ( 'Calendar' ),
CALCULATE (
COUNTROWS ( data ),
FILTER (
data,
AND (
data[receipt_date] <= MAX ( 'Calendar'[Date] ),
OR (
data[closure_date] >= MIN ( 'Calendar'[Date] ),
data[closure_date] = BLANK ()
)
)
)
)
)

 

https://www.dropbox.com/s/d26henlpvp41c87/rjesakv2.pbix?dl=0 

 

 

Hi, My name is Jihwan Kim.

 

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

 

Linkedin: https://www.linkedin.com/in/jihwankim1975/