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
TCavins
Helper V
Helper V

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!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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)

 

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!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
amitchandak
Super User
Super User

@TCavins , A new measure 

 

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

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

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.