How to use an OR operator between FILTER statements in a CALCULATE expression?

[soja]

Hello, 

I am still fairly new to DAX and Power BI and am having trouble solving a problem.  I am trying to figure out how to write an expression (in a measure) that evaluates a 8 variables (which have been assigned rank to make it more straight fwd).   Basically, I want to have the first filter followed by and AND statement (which is currently correct) and then have all the rest of the filters be OR statements.  However, the last 2 filter variables exist in a different table which require me to use another FILTER statement with RELATEDTABLE to access.  Is there a way to link the last 2 FILTERs with OR statements instead of the AND statement assumed by the comma?

What I have so far is this (which produces an answer that underestimates the actual results due to the additional AND statements):

Indicator = CALCULATE(COUNTX('Table1', 'Table1'[IDNumber]),

    FILTER('Table1', [Rank1] >=4||[Listed?]="yes"),

    FILTER('Table1', [Rank2] >=13||[Rank3]>=15||[Rank4]>=15||[Rank5]>=6||[Rank6]>=10),

    FILTER(RELATEDTABLE(Table2), Table2[Rank7]>=8),

    FILTER(RELATEDTABLE(Table3), Table3[Rank8]>=5))

Conversely, if I could create another calculated column in Table1 for the Rank7 and Rank8, that would also work.  But I am not sure how to assign rank to a column from a related table in a calculated column.  If there is a way to do this, that would also solve my problem.  (NOTE:  All tables have appropriate relationships and are joined through the "IDNumber", though some have many-to-one relationships).

Thank you in advance for you help.

[v-yuta-msft · Accepted solution]

Hi soja,

I'm afraid you can't use OR() function directly, as a workaround, why not merge table2 and table3 based on IDNumber column as a new table firstly? Then you can use DAX like:

result =
FILTER (
    RELATEDTABLE ( newtable ),
    newtable[rank7] >= 8
        || newtable[rank8] >= 5
)

Regards,

Jimmy Tao

[Greg_Deckler]

Might be able to use OR function, https://msdn.microsoft.com/en-us/query-bi/dax/or-function-dax

Sample data would be helpful, Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490