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/

v-kkf-msft's avatar
v-kkf-msft
Community Support
5 years ago

Hi Rjesak ,

Try the following formula:

Measure = 
var tab = 
    CALCULATETABLE(
        VALUES('Table'[Unique_seq]),
        FILTER(
            'Table',
            'Table'[reciept_cy] = [Current_CY] 
            || 'Table'[closure_cy] = [Current_CY] 
            || ( NOT(ISBLANK('Table'[receipt_date]))
            && NOT(ISBLANK('Table'[closure_date])) )
        )
    )
return 
    COUNTROWS(
        INTERSECT(
            tab,
            VALUES('Table'[Unique_seq])
        )
    )

 

Then filter the Unique_seq field in the visuals:

  • Filter type: Top N
  • Show items: Top 1
  • By value: Measure

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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