Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Rank In PBI

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 Highe...
Need Help
Show and Tell
v-yueyunzh-msft's avatar
v-yueyunzh-msft
Community Support
3 years ago

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's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      v-yueyunzh-msft
      Community Support
      3 years ago

      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's avatar
        Anonymous
        Not applicable
        3 years ago


        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")))