Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jguercio
Frequent Visitor

COUNTROWS Returning Wrong Value

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?

 

jguercio_0-1643299739594.png

 

 

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 )

 

 

jguercio_1-1643299862533.png

 

1 ACCEPTED SOLUTION

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

View solution in original post

2 REPLIES 2
goncalogeraldes
Super User
Super User

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors