Create Measure: Measure that counts instances that populates a certain amount of times in a table

[AW1976NOVA]

Hello,

I created another thread earlier about this but the solution proposed was not what I needed.

I need to create a measure that will count the number of [Member ID] that populate in a table 2 times.  

Here is an example set of data:

The measure should return the value of 4.  It should return the value of 4 because 5454, 1111, 777, and 1222 each populate 2 times.  The other Member IDs populate either more or less than 2 and are not counted.

I would like this to be all included within the DAX of a single measure without the need to further filter it.

Please let me know if you need more detail.  I will be closely monitoring the thread for follow up questions.

Thank you,

Andrew

[Samarth_18 · Accepted solution]

Hi @AW1976NOVA ,

Please try this:-

Measure =
COUNTROWS (
    FILTER (
        SUMMARIZECOLUMNS ( 'member'[Member ID], "Total", COUNT ( 'member'[Member ID] ) ),
        [total] = 2
    )
)

Thanks,

Samarth

[AW1976NOVA]

Hi,

I have an excel table that I'm bringing in to Power BI.  I need to count the number of instances a Car Brand populates 2 times in this table.  For example,

Car Brand          Engine          Color

Ford                   351               Red

Chevy                 427               White

BMW                  225               Green

Ford                   302                Black

Ford                   390                Black

BMW                  338               White

Jeep                    236               Brown

Jeep                    340               White

Based on the data above, (if we focus on the Car Brand column) I need the measure to return the value of 2.  It should/needs to return the value of 2 because only Jeep and BMW populated twice.  The other Car Brands (Ford and Chevy) populated 3 times and 1 time respectively.

What is the correct DAX measure I would need to write in order to accomplish this?

Thank you,

Andrew

[Samarth_18]

Hi @AW1976NOVA ,

Create a measure with below code and use it as filter on your visual:-

_filter =
COUNTROWS ( FILTER ( ALL ( Cars ), Cars[Car Brand] = MAX ( Cars[Car Brand] ) ) )

Output:-

Thanks,

Samarth

[Samarth_18 · Accepted solution]

Hi @AW1976NOVA ,

Please try this:-

Measure =
COUNTROWS (
    FILTER (
        SUMMARIZECOLUMNS ( 'member'[Member ID], "Total", COUNT ( 'member'[Member ID] ) ),
        [total] = 2
    )
)

Thanks,

Samarth

[AW1976NOVA · Accepted solution]

Thank you Samarth.  That worked but I have a follow up question:

If I wanted to add a filter to the DAX you provided that counted only the [Member ID] where [Employee] = "Y".......how would I add that to the DAX?

In this case the measure should only return the value of 1 since only 7777 populated 2 times but also had a value of "Y" for [Employee]

[Samarth_18]

@AW1976NOVA , Try this:-

Measure =
COUNTROWS (
    FILTER (
        SUMMARIZECOLUMNS (
            'member'[Member ID],
            'member'[Employee],
            "Total", COUNT ( 'member'[Member ID] )
        ),
        [total] = 2
            && [Employee] = "Y"
    )
)