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
scalonia
Regular Visitor

Distinct Count with Multiple Conditions

How can I create a distinct count with this condition? Status column will be completed once the new routine column has value.

35027C08-EF9E-412B-A097-CA4802699F01.png

53811752-1DB5-421B-91C5-CF2647901D3E.png

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @scalonia 

please try

Status =
VAR T1 =
    CALCULATETABLE ( 'Table 1', ALLEXCEPT ( 'Table 1', 'Table 1'[Entity ID] ) )
VAR T2 =
    FILTER ( T1, 'Table 1'[New Routine] <> BLANK () )
RETURN
    IF (
        COUNTROWS ( T1 ) = COUNTROWS ( T2 )
            && COUNTROWS ( T2 ) > 1,
        "Completed",
        "Not Completed"
    )

View solution in original post

7 REPLIES 7
tamerj1
Super User
Super User

Hi @scalonia 

please try

Status =
VAR T1 =
    CALCULATETABLE ( 'Table 1', ALLEXCEPT ( 'Table 1', 'Table 1'[Entity ID] ) )
VAR T2 =
    FILTER ( T1, 'Table 1'[New Routine] <> BLANK () )
RETURN
    IF (
        COUNTROWS ( T1 ) = COUNTROWS ( T2 )
            && COUNTROWS ( T2 ) > 1,
        "Completed",
        "Not Completed"
    )

Would you be able to help me on this? For this entity #, if 1 is yes, then the result is satisfied.

C38FE107-D51E-47A5-BE00-0EFD8FEA4E3D.png

1DF167FA-CE9A-494C-B14F-C4B448A3B6E0.png

  

@scalonia 

Status 2 =
VAR T1 =
    CALCULATETABLE (
        VALUES ( 'Table 1'[New Routine] ),
        ALLEXCEPT ( 'Table 1', 'Table 1'[Entity ID] )
    )
RETURN
    IF ( "Yes" IN T1, "Satisfied", "Not Satisfied" )

Did not work

It works! Thank you!

scalonia
Regular Visitor

Up

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