DAX: Count IDs that have a specific value in ColumnB 3 or more times.

[TCavins]

I'm trying to create a measure that gives me the count of ID's (Column A) where column B = 'xxx' and it occurs 3 or more times. For the following table, the measure would return 2(IDs 1 and 3 have XXX 3 or more times). However, I'm struggling with the DAX for the measure. Any ideas? Assume table is named MyTable.

ID	ColumnB
1	XXX
2	ABC
1	XXX
1	XXX
3	XXX
3	XXX
3	XXX

[v-kelly-msft]

Hi  @TCavins ,

Create 2 measures as below:

Count1 =
VAR _count1 =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[ID] = MAX ( 'Table'[ID] )
                && 'Table'[ColumnB] = MAX ( 'Table'[ColumnB] )
        )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[ID] ),
        FILTER ( ALL ( 'Table' ), _count1 >= 3 && 'Table'[ID] = MAX ( 'Table'[ID] ) )
    )

Count =
SUMX ( VALUES ( 'Table'[ID] ), 'Table'[Count1] )

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[smpa01]

@TCavins  try this

Measure =
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        GROUPBY (
            CALCULATETABLE (
                'Table',
                FILTER ( VALUES ( 'Table'[ColumnB] ), 'Table'[ColumnB] IN { "XXX" } )
            ),
            'Table'[ID],
            "X", COUNTX ( CURRENTGROUP (), [ColumnB] )
        ),
        [X] >= 3
    )
)

or 

Measure2 =
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        ADDCOLUMNS (
            SUMMARIZE (
                FILTER ( 'Table', 'Table'[ColumnB] = "XXX" ),
                'Table'[ID],
                'Table'[ColumnB]
            ),
            "X", CALCULATE ( COUNT ( 'Table'[ColumnB] ) )
        ),
        [X] >= 3
    )
)

[TCavins · Accepted solution]

I was able to get it to work in two steps. Creating a table, then a measure off of that table. Still trying to figure out how to do it all in one step. Added a column in the grouping to get people by provider that have 3 or more values of XXX in the ColumnB table.

IC =
SUMMARIZE(
    FILTER(
        'Table',
        'Table'[ColumnB] = "XXX"),
        'Table'[ID],
        'Table'[ProviderName],
        "tc", COUNT('Table'[ID])
)

MEASURE4 =
CALCULATE(COUNTROWS(IC), IC[tc] >=3)

[v-kelly-msft]

Hi  @TCavins ,

Glad to hear it.

Could you pls mark the reply as answered to close it?

If possible,would you pls also mark other correct replies as well.Thus others could also check them.

Thanks in advance.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[smpa01]

@TCavins  well done. Make sure you understand the secrets of SUMMARIZE if you are to perform aggregation inside SUMMARIZE. 

https://www.sqlbi.com/articles/all-the-secrets-of-summarize/

[TCavins]

I'm not getting the correct results with either of those.

[smpa01]

@TCavins  my solution is based on the data you gave me and as you can see the measure is performing as desired in the sample dataset you gave. Is there something you did not include?

[TCavins]

I needed a count of IDs that have have XXX 3 or more times.

The measure should return 2 because there are 2 IDs that have XXX at least 3 times in ColumnB.

[smpa01]

@TCavins  change to this

Measure2 = 
CALCULATE (
    DISTINCTCOUNT('Table'[ID]),
    FILTER (
        ADDCOLUMNS (
            SUMMARIZE (
                FILTER ( 'Table', 'Table'[ColumnB] = "XXX" ),
                'Table'[ID],
                'Table'[ColumnB]
            ),
            "X", CALCULATE ( COUNT ( 'Table'[ColumnB] ) )
        ),
        [X] >= 3
    )
)

[amitchandak]

@TCavins , A new measure 

countx(filter(summarize(Table, Table[ID],"_1" ,Count(Table[columnb])) ,[_1] >=3) ,[ID])

[TCavins]

@amitchandak Where in the measure is it checking if ColumnB = "XXX"?