Forum Discussion

NoobAnalyst01's avatar
NoobAnalyst01
Helper I
8 years ago
Solved

Count numbers based on two conditions for OR statement

Hello,

 

I have three tables, join with order ID on 1:1 relationship.

 

Table A has all orders information 

Table B has orders that are scaned as late

Table C has oerder that are repored as late

(so B and C may has same order ID, same you can report it as late but at the same time report it as late)

 

so I want to count in Table A, how many unqiue orders are scaned as late or report as late

 

I don't know how to do that, but I tried tha following formula, it gives me the number, just want to make sure if there any another good solution or I am lucky to get the best one?

 

Lates=CALCULATE(DISTINCTCOUNT('A'[SALE_ID]),filter('A',RELATED(C[Report_Late])<>blank()||RELATED(B[LATE_FLAG])="Y"))

 

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    HI NoobAnalyst01,

     

    I'd like to suggest you use below measure to get matched row count:

    distinc =
COUNTROWS (
    FILTER (
        ALL ( TableA ),
        TableA[SALE_ID]
            IN UNION (
                INTERSECT ( VALUES ( TableA[SALE_ID] ), VALUES ( TableB[SALE_ID] ) ),
                INTERSECT ( VALUES ( TableA[SALE_ID] ), VALUES ( TableC[SALE_ID] ) )
            )
    )
)

    Regards,

    Xiaoxin Sheng

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    HI NoobAnalyst01,

     

    I'd like to suggest you use below measure to get matched row count:

    distinc =
COUNTROWS (
    FILTER (
        ALL ( TableA ),
        TableA[SALE_ID]
            IN UNION (
                INTERSECT ( VALUES ( TableA[SALE_ID] ), VALUES ( TableB[SALE_ID] ) ),
                INTERSECT ( VALUES ( TableA[SALE_ID] ), VALUES ( TableC[SALE_ID] ) )
            )
    )
)

    Regards,

    Xiaoxin Sheng