Count values = 1 in a measure

[zapppsr]

Hi. I have a measure like this:

Visits = COUNTA(T_Visits[Oportunities])

It results the number of visits. If I associate it with a company it goes like this:

Company name - Visits
Company A - 300
Company B - 200
Company C - 123
Company D - 1
Company E - 1

Since there are lots of companies with only one visit, I want to know how many companies have only one visit.

How can I do that?

 

[zapppsr]

Playing with the formula, I tried to see how many companies have more than 20 visits and changed [cnt]=1 to [cnt]>20

It resulted 245, which is the sum of the number of visits of the 10 companies that have more than 20 visits. Do you know why?
[TABLE="width: 170"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]35[/TD]
[/TR]
[TR]
[TD="align: right"]26[/TD]
[/TR]
[TR]
[TD="align: right"]26[/TD]
[/TR]
[TR]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD="align: right"]24[/TD]
[/TR]
[TR]
[TD="align: right"]23[/TD]
[/TR]
[TR]
[TD="align: right"]22[/TD]
[/TR]
[TR]
[TD="align: right"]22[/TD]
[/TR]
[TR]
[TD="align: right"]21[/TD]
[/TR]
[TR]
[TD="align: right"]21[/TD]
[/TR]
</tbody>[/TABLE]

 

[anvg]

That my measure is simplified because rows of T_Visits table has really one row per a company only. But for your new case

CALCULATE(COUNTROWS(
    SUMMARIZE('T_Visits',T_Visits[Company],"cnt",COUNTA(T_Visits[Company]))
    ),
    FILTER(
        SUMMARIZE('T_Visits',T_Visits[Company],"cnt",COUNTA(T_Visits[Company])),
        [cnt]>20
    )
)

Maybe Matt will make that simpler
Regards,

 

[zapppsr]

Perfect again! Thanks anvg

 

[anvg]

It is simpler

COUNTROWS(
    FILTER(SUMMARIZE('T_Visits',T_Visits[Company],"cnt",COUNTA('T_Visits'[Company])),[cnt]>1)
)

 

[zapppsr]

Genius!! Worked too!

 

[zapppsr]

Hi anvg. That "cnt" in the expression, is like creating a virtual column/field where the values are summarized, and then we use it as a field later on [cnt]>1?

For my limited understanding of DAX, I guess it could be any string there... or is it a function too?

I'm very new to DAX, do you recommend any readings or videos to learn nice expressions such the one you taught me?

And thanks again!

 

[anvg]

Hi. zapppsr.
I am sorry. I was not near any computer yesterday. My answer is for last solution.
SUMMARIZE function gets a rows subset of 'T_Visists' table which is defined by filter content of a current pivot cell. SUMMARIZE function splits rows from that subset for each equal 'Company' name and computes a count into a 'cnt' virtual column. At result we have a virtual calculated table.
FILTER function allows a row of that virtual table goes to COUNTROWS function if it has 'cnt'>20. And COUNTROWS simple considers those filtered rows.

do you recommend any readings or videos to learn

You could buy and read Matt Allington book Learn to Write DAX - Excelerator BI and his , Blog - Excelerator BI and read https://www.sqlbi.com/articles/
and maybe read https://www.sqlbi.com/books/the-definitive-guide-to-dax/ but it is not simple for beginner.
I know English badly and never looked at any video about Power Pivot
Regards, Andrey.