Rank In PBI

[Anonymous]

Hi all,

I have

Views in Fact User table and News type in Dim Content table and Content title in Dim Content table

For each news type I want to see Top 1 Content title which is having Highest views

Like for Global News I want to see what is the highest views content tile
For Local news I want to see what is the highest views content tile
For regional news I want to see what is the highest views content tile

PLease help
Thanks in advance

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file and show the expected result.

[v-yueyunzh-msft]

Hi , @Anonymous 

According to your description,you want to "see Top 1 Content title which is having Highest views".

Here are the steps you can refer to :
(1)This is my test data:

(2)We can create a measure like this:

Measure = MAXX( TOPN( 1, ADDCOLUMNS(VALUES('Dim Content Table'[Content title]),"Views", CALCULATE( SUM('Fact User table'[Views]))) ,[Views]) ,[Content title])

Then we can put the measure on the visual and we can meet your need:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem. (You can also upload you sample .pbix [without sensitive data] to the OneDrive and share with the OneDrive link to me ! )

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anonymous]

Tables relationships:

Fact content to Dim table: Many to one
Fact content to Fact user: Many to Many

Please consider the relationships I created and please help me on this

[v-yueyunzh-msft]

Hi, @Anonymous 

Thanks for your quick response and the relationship image about your model.

For your need , you want to get the "For each news type I want to see Top 1 Content title which is having Highest views".

But for the [news type] in your 'Dim Table' is a measure (if a measure , how do you get this)? Secondly , the [news type] and the [Content_title] is one-to-many?

Can you give me some simple test data for these three tables and the end result you want to get in a table format. This will make the case very easy!

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anonymous]

This is the sample data I created in excel for your reference

I have created many to one from Fact Content to Fact User on ID Column
and Many to many from Fact Content to Fact User on User ID


On News type
I have datas_ column based on that I created 

VAR _c=if(SEARCH("China",'Dim_table'[datas_],1,0)>0,1,0) VAR _ee=if(SEARCH("Enlarged Europe",'Dim_table'[datas_],1,0)>0,1,0) VAR iap=if(SEARCH("India and Asia Pacific",'Dim_table'[datas_],1,0)>0,1,0) VAR mea=if(SEARCH("Middle East & Africa",'Dim_table'[datas_],1,0)>0,1,0) VAR na=if(SEARCH("North America",'Dim_table'[datas_],1,0)>0,1,0) VAR sa=if(SEARCH("South America",'Dim_table'[datas_],1,0)>0,1,0) var g=if(SEARCH("Global",'Dim_table[datas_],1,0)>0,1,0) return if(g=1,"Globe News",if(_c+_ee+iap+mea+na+sa>1,"Local News",if(_c+_ee+iap+mea+na+sa=1,"Region News")))

[v-yueyunzh-msft]

Hi, @Anonymous 

Thanks for your quick response and your sample data first!

I create some test data like this and this is my understand:

And this is the relationship:

If this , the 'Dim Content' table can directly filter the 'Fact User' Table .

We can create a measure like this:

Measure 2 = MAXX( TOPN(1, ADDCOLUMNS( CALCULATETABLE( SUMMARIZE('Dim Content','Dim Content'[ID],'Dim Content'[Content tiltle]) , ALLEXCEPT('Dim Content','Dim Content'[News Type])   ) , "Views" , CALCULATE(SUM('Fact User'[Views]))) ,[Views] ,DESC) , [Content tiltle])

Then we can get the result as follows:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem. (You can also upload you sample .pbix [without sensitive data] to the OneDrive and share with the OneDrive link to me ! )

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anonymous]

Hi,
Thanks a lot
In the same way I used MINX function to show minimum viewwd content title, but it has shown same views for both Highest and lowest viewed content titles

Please help on this
Thanks

[v-yueyunzh-msft]

Hi , @Anonymous 

Thanks for your quick response!According to your description, you want to show the last1 content title , if this , you can replace the DESC to ASC to check if it can meet your need.

Measure 2 = MAXX( TOPN(1, ADDCOLUMNS( CALCULATETABLE( SUMMARIZE('Dim Content','Dim Content'[ID],'Dim Content'[Content tiltle]) , ALLEXCEPT('Dim Content','Dim Content'[News Type])   ) , "Views" , CALCULATE(SUM('Fact User'[Views]))) ,[Views] ,ASC) , [Content tiltle])

The MAXX() in this dax code just get the text in the table got from TOPN() function.So when you replace the MAXX() to MINX() , it will show the same in your visual.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anonymous]

Hi,

Thanks a lot for replying, actually for top views and bottom views it showing same views count...
Can you please help me on resolving this issue

[v-yueyunzh-msft]

Hi, @Anonymous 

Thanks for your quick response!

I test it in my sample pbix file , it seems work .

You can check my pbix file and if we may have some difference in relationships?

Or maybe I've misunderstood your needs too?

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anonymous]

Hi,
Thank you so much for replying
It helped

I am trying to show views for that comtent title also, butr what the issue i am facing here is instead of showing the view number for that particular content for a particular news type, it's showing summation of whole news type and showing for a content type

For example:
For Global News Highets viewed content title is GT News1 it must show views number only for that content title
But it's showing summation of whole views for that Globe News News type and same for local news and region news

actually views column is summarized column

Please help me

Thanks in advance

[v-yueyunzh-msft]

Hi , @Anonymous 

Thanks for your quick response !

According to your description, you want to show views.

I create two measures you can check if it can meet your need:

Value Per News Type = CALCULATE( SUM('Fact User'[Views]) , ALLEXCEPT('Dim Content','Dim Content'[News Type]))

Max Value = CALCULATE( SUM('Fact User'[Views]) , TREATAS({[Max Value of Content title]} , 'Dim Content'[Content tiltle]) , ALLEXCEPT('Dim Content','Dim Content'[News Type])   )

We can test it in the visual , the reuslt is as follows:

If this still does not meet your needs, you can provide me with the final results you want in a form based on my test data.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anonymous]

Hey Hi,

Thanks alot for replying to all the help

I will place the example data from excel here
Here I just want to see content title which are having highest views
For each news type I want to see content title which are having highest views

News Type	Content Title	Views
Global	team to lead Company through increasingly challenging external environment	11,900
Region	Competitors are coming	4,596
Region	Team at exhibition	3,794
Global	New Iconic	3538
Loaclity	Solution for disabilities	1,596

For Global, Region and Locality I want to see only top view content title

And I just have only one table that is Fact tavle no other tables are there

can you please help??

Thanks in advance

[v-yueyunzh-msft]

HI , @Anonymous 

Thanks for your quick response!

According to your description, you only have one table and you want to show the highest content title. This is my understand and test data for your need:

If this , we canc reate a measure like this:

Measure = 
var _cur_type= MAX('Table'[News Type])
var _t  =CALCULATETABLE( SUMMARIZE('Table','Table'[News Type],'Table'[Content Title],"views" , SUM('Table'[Views])) , ALLEXCEPT('Table','Table'[News Type]))

return
IF(  MAXX(topn(1,_t,[views],DESC) , [Content Title])  = MAX('Table'[Content Title]) , 1, -1)

Then we can put this measrue on the "Filter on this visual":

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem. (You can also upload you sample .pbix [without sensitive data] to the OneDrive and share with the OneDrive link to me ! )

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anonymous]

Hi,

Thanks for replying...

This is not working
can we do it with rank function if possible?

[v-yueyunzh-msft]

Hi , @Anonymous 

Thanks for your quick response! Soorry for that this dax code can not work in your side . Did you not put [News Type] as dimension in your visual so that the dax code will not work.

If you just put the [Content Title] in the visual , you can use this dax code:

Measure 2 = 
var _cur_type= MAX('Table'[News Type])
var _t  =CALCULATETABLE( SUMMARIZE(ALLSELECTED('Table'),'Table'[News Type],'Table'[Content Title],"views" ,CALCULATE( SUM('Table'[Views]))) )
var _t2  = FILTER(_t, [News Type] = _cur_type)
return
IF(  MAXX(topn(1,_t2,[views],DESC) , [Content Title])  = MAX('Table'[Content Title]) , 1, -1)

Since placing different fields will result in different filtering contexts, if this DAX code still cannot meet your needs, you can provide me with the fields you need to place and your final results in a table form.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anonymous]

Hi,
I am adding News type as dimension only.

I am adding content title, News type, Views, Published date, created by name

I am filtering it by date

For example If i select between 1st june 2023 to 30th june 2023 it must show only one top view content title for each news

and there is many published dates for one date 
For example for 1st june 2023 there are many published dates

Here view is , content title viewd by user on that date 

Published date is date when the content is published


all this columns i am adding

Please reply
Thanks

[v-yueyunzh-msft]

Hi , @Anonymous 

Thanks for your quick response! According to your description, this is my understand:

If this is the same as yours table structure and the end result is right, you can refer to :
(1)We can create two measures:

MAX views = var _t = SUMMARIZE('Table','Table'[Content Title],"value", SUM('Table'[Views]))
var _t2 = TOPN(1,_t , [value] , DESC )

return
SUMX(_t2 , [value])

MAX COntent Title = var _t = SUMMARIZE('Table','Table'[Content Title],"value", SUM('Table'[Views]))
var _t2 = TOPN(1,_t , [value] , DESC )

return
maxx(_t2,[Content Title])

(2)Then we can put it on the visual and just put the [News type] as the dimension only!

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem. (You can also upload you sample .pbix [without sensitive data] to the OneDrive and share with the OneDrive link to me ! )

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anonymous]

Hi Aniya zhang,
Thanks for being patient and replying to my problem

News Type	Content Title	Views
Global	team to lead Company through increasingly challenging external environment	11,900
Region	Competitors are coming	4,596
Region	Team at exhibition	3,794

I just need to show this 
only one top content title and it's respective view for each news type

but when i am adding created by name, published date dimension columns into the table
it's showing many values

I'll paste the dax i have used below

Top View = IF ( ISINSCOPE( 'Fact Content'[content_title] ), RANKX ( CALCULATETABLE ( VALUES ( 'Fact Content'[content_title] ), ALLSELECTED( ( 'Fact Content'[content_title] ), 'Fact Content'[Filtered News Type]) ), 'Fact Content'[Month Filter] ) )

Month Filter = CALCULATE(SUM('Fact Content'[nb_content_views]), MONTH('Fact Content'[date]) = MONTH(MAX('Fact Content'[date])) - 1)

the below is the output i am getting

But after adding created by name , published_at columns it's splitting down to many rows like below

Please helpp

It's very important for me

Thanks
sai

[v-yueyunzh-msft]

Hi , @Anonymous 

Thanks for your quick response! According to your screenshot and your dax code . The [Month Filter] measure seems to calculate the views in the previous month . And the [Top View] is used to filter the highest views in your table visual and it work good in your screenshot . And the issue is that when you put the other field like [Created By Name] or the [Published Date] , the table visual will not work.

For this, i think this not the reason for your [Top view] measure. Due to your Views is the previous month so you can not just directly put the other fields to the visual.I also test it in my side , but i still have no sample data as the same as yours so I can't completely guarantee that my situation is exactly the same as yours.

This is my test data and my understand now:

For this i use your two measures you provided and it work good in my side .If you want to add the [Created By Name] or the [Published Date], you may need to create measures to realize it:

Created Name Measure = CALCULATE( MAX('Fact Content'[created by name]) , MONTH('Fact Content'[date]) = MONTH(MAX('Fact Content'[date])) - 1)

Published Date Measure = CALCULATE( MAX('Fact Content'[published_at]) , MONTH('Fact Content'[date]) = MONTH(MAX('Fact Content'[date])) - 1)

Then we can get the right value due to it is from previous month so we can not just directly put it on the visual.

If this method still can not help you solve this case , can you just share me some sample data as the same as yours in a table format , and your end result you need also.Inconsistencies in data structures can complicate things very much, and [Filtered News Type] also appears in your DAX, I'm not sure what the relationship is to [News Type]?Hope this solution can help you!

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly