cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TCavins
Resolver I
Resolver I

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

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.


IDColumnB
1XXX
2ABC
1XXX
1XXX
3XXX
3XXX
3XXX

 

1 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)

 

View solution in original post

11 REPLIES 11
v-kelly-msft
Community Support
Community Support

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:

vkellymsft_0-1632736715534.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

smpa01
Super User
Super User

@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
    )
)

 

 

smpa01_0-1632508835874.png

 





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

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


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)

 

View solution in original post

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!

@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/

 





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

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


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

@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?





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

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


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.


@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
    )
)

 

 

smpa01_0-1632514578475.png

 





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

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


amitchandak
Super User
Super User

@TCavins , A new measure 

 

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!