Count the number of rows that share the same value

[Anonymous]

Hello!

I am trying to calculate how many of my keywords share the same position. I have a table that has 3 columns, Date, Keywords and Position.

Date         Keywords       Position

######    term 1            4

######    term 2            1

######    term 3            2

######    term 4            3

######    term 5            4

######    term 6            1

######    term 7            2

I am want to create a measure that returns the number of terms that share the same position

So the output will be like 5 terms on position 1, 2 terms on potions 2 etc...

Is this possible?

Thank you!

[Sean]

1) Create a Table Visual and drag Position and this Measure to it

Measure = DISTINCTCOUNT ( 'Table'[Keywords] )

2) You can also achieve the same result by draging Position and Keywords to a Table Visual

then right-click on Keywords in the Value area and change to Count(Distinct)

[Anonymous]

Thank you for your reply, Sean.

It is working. However, If a term is repeated in the column keyword and every time has a different position value, how this will work?

When I display the data by date the output is correct, by when I display the data by month, some values are counted more than once.

Regards

George

[v-sihou-msft]

@Anonymous

If you build your date hierarchy properly, using DISTINCTCOUNT() will return correct result on different level. In this scenario, your calculation only need to group on POSITION level.

Measure = CALCULATE(DISTINCTCOUNT ( 'Table'[Keywords] ),ALLEXCEPT('Table',Table[Position]))

Regards, 

[Sean]

Can you provide some sample data and specify how exactly will the results be displayed? (you may need a count not distinct)

[Anonymous]

Hello Sean,

Thank you for sharing your GIF. I have managed to get the same results are you did.

Now I am trying to use a line chart to display the data, but it doesn't work.

I am trying to recreate the line chart we are using on our system in PowerBi which shows the number of keywords for each position per month. Ideally, i would like to have a chart that shows the footprint of one month and provide a visual comparison with the previous month. Like the example below.

My table looks like this

It included keywords that the site appears for every day and the position that it appeared for.

Eventually, i would like to see on a graph the number of keywords per position per day, week, month etc.

Regards

George