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
Anonymous
Not applicable

Creating a count IF certain conditions are met

Hello, I have been trying to create a count of tests done by employees but there are certain parameters I want to include. They are required to do 2 per week, and I need to create a count to see how many of these mandatory tests have been completed. I can't just use the COUNT or SUM functions because this counts the total number done and if an employee has done more than 2, it throws off the total. Basically. I want to create a measure that counts the number of tests for each employee, and if it is 2 or higher, only count 2. I also can't do IF(COUNT(emplyee name)>2,2,COUNT(employee name)) because this is checking if the count of ALL employees is greater than 2, and i need to check for individual employees. My data table is a list of tests done, so emplyee names will be repeated. I could create a new table of employee names and create a calculated column based off of the counts of tests done, but I wanted to see if anyone knew how to achieve this with a measure.

1 ACCEPTED SOLUTION
v-yanjiang-msft
Community Support
Community Support

Hi @Anonymous ,

According to your description, I create a sample.

vkalyjmsft_0-1661222381266.png

Here's my solution, create a measure.

Count =
VAR _T =
    ADDCOLUMNS (
        'Table',
        "Count",
            IF (
                COUNTROWS (
                    FILTER ( 'Table', 'Table'[Employee Nmae] = EARLIER ( 'Table'[Employee Nmae] ) )
                ) > 2,
                2
                    / COUNTROWS (
                        FILTER ( 'Table', 'Table'[Employee Nmae] = EARLIER ( 'Table'[Employee Nmae] ) )
                    ),
                1
            )
    )
RETURN
    SUMX ( _T, [Count] )

Get the correct result.

vkalyjmsft_1-1661222449978.png

I attach my sample below for your reference.

 

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-yanjiang-msft
Community Support
Community Support

Hi @Anonymous ,

According to your description, I create a sample.

vkalyjmsft_0-1661222381266.png

Here's my solution, create a measure.

Count =
VAR _T =
    ADDCOLUMNS (
        'Table',
        "Count",
            IF (
                COUNTROWS (
                    FILTER ( 'Table', 'Table'[Employee Nmae] = EARLIER ( 'Table'[Employee Nmae] ) )
                ) > 2,
                2
                    / COUNTROWS (
                        FILTER ( 'Table', 'Table'[Employee Nmae] = EARLIER ( 'Table'[Employee Nmae] ) )
                    ),
                1
            )
    )
RETURN
    SUMX ( _T, [Count] )

Get the correct result.

vkalyjmsft_1-1661222449978.png

I attach my sample below for your reference.

 

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak
Super User
Super User

@Anonymous , You need measures like

 

Test count = count(Table[Test])

 

test GT 2  = Sumx(filter(Values(Table[Employee]),[Test Count]>2), [Test Count])

 

test GT 2 cnt = Countx(filter(Values(Table[Employee]),[Test Count]>2), [Employee])

 

weekly test GT 2  = Sumx(filter(Summarize(Table, Table[Employee], Table[Week], "_1",[Test Count]) , [_1 ]>2), [_1]) 

Anonymous
Not applicable

Could you explain the "_1" to me?

 

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.