COUNTROWS Returning Wrong Value

[jguercio]

I have a calculated column called Service Authorized in the FACT-Services table. Its intention is to make sure there is an authorization in place (FACT-BH Authorizations table) for the date of service. This DAX logic has performed in other contexts. The end result is a COUNTROWS of a table of any authorizations that meet the conditions. This will eventually become binary (1/0) but there is a problem with multiple overlapping authorizations, which the COUNTROWS should help track down.

For one case in question, the result of the calculated column is 5, indicating that there are 5 overlapping authorizations during the service date. But when I create a calculated table to see what is in the count, it only shows 1 row. 

Any insights into this? Is there something in my DAX that is causing this?

Service Authorized =
VAR AuthTable =
    FILTER(
         'FACT-BH Authorizations' ,
         'FACT-Service'[Client ID]  =  'FACT-BH Authorizations'[Client ID] 
            &&  'FACT-Service'[Contact Date]  >=  'FACT-BH Authorizations'[AuthStart] 
            &&  'FACT-Service'[Contact Date]  <=  'FACT-BH Authorizations'[AuthEnd] 
            && IF(
                 'FACT-Service'[Program ID]  = "30001013",
                 'FACT-BH Authorizations'[UCode]  = "U749",
                IF(
                     'FACT-Service'[Program ID]  = "30001075",
                     'FACT-BH Authorizations'[UCode]  = "U649",
                    IF(
                        OR(  'FACT-Service'[Program ID]  = "30001014",  'FACT-Service'[Program ID]  = "30001015" ),
                         'FACT-BH Authorizations'[UCode]  = "U349",
                        IF(
                             'FACT-Service'[Program ID]  = "30001005",
                              'FACT-BH Authorizations'[UCode]  = "U749" 
                             ||  'FACT-BH Authorizations'[UCode]  = "U649"
                             ||  'FACT-BH Authorizations'[UCode]  = "U349"
                        )
                    )
                )
            )
    )
RETURN
COUNTROWS( AuthTable )

[goncalogeraldes]

Hello there @jguercio ! Have you tried with a Summarize?

Service Authorized =
VAR AuthTable =
SUMMARIZE(
    FILTER(
         'FACT-BH Authorizations' ,
         'FACT-Service'[Client ID]  =  'FACT-BH Authorizations'[Client ID] 
            &&  'FACT-Service'[Contact Date]  >=  'FACT-BH Authorizations'[AuthStart] 
            &&  'FACT-Service'[Contact Date]  <=  'FACT-BH Authorizations'[AuthEnd] 
            && IF(
                 'FACT-Service'[Program ID]  = "30001013",
                 'FACT-BH Authorizations'[UCode]  = "U749",
                IF(
                     'FACT-Service'[Program ID]  = "30001075",
                     'FACT-BH Authorizations'[UCode]  = "U649",
                    IF(
                        OR(  'FACT-Service'[Program ID]  = "30001014",  'FACT-Service'[Program ID]  = "30001015" ),
                         'FACT-BH Authorizations'[UCode]  = "U349",
                        IF(
                             'FACT-Service'[Program ID]  = "30001005",
                              'FACT-BH Authorizations'[UCode]  = "U749" 
                             ||  'FACT-BH Authorizations'[UCode]  = "U649"
                             ||  'FACT-BH Authorizations'[UCode]  = "U349"
                        )
                    )
                )
            )
    ),
    'FACT-Service'[Client ID], #for example
    "Rows", 1
)
RETURN
SUM( [Rows] )

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Gonçalo Geraldes

[jguercio · Accepted solution]

Ultimately, there was a problem with the relationships that was fixed. However, your solution also works. Thank you.