Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
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)
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
@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,
Can you provide some sample data and specify how exactly will the results be displayed? (you may need a count not distinct)
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |