Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply

Top items of each group, with information from other tables

I wanted help creating a dax that would return the 3 most downloaded questions for each discipline. This will be presented in a table format.
So, for each discipline, bring the 3 questions that were most downloaded in a given period, in addition to the subject and the question, the table must have information on the degree of difficulty, type of question, text and answer answer for the question.
I am attaching a pbix with the example data.
https://drive.google.com/file/d/1L7zJwGZlSa02TMZ9tgLenIc0bqeH8Z-x/view?usp=sharing 

1 ACCEPTED SOLUTION

One of the problems you have in your model is many-to-many relationship, which is not recommended.

I've used the second approach from the article along with a bridge table:

ERD_0-1694066097983.png

questions_loaded = CALCULATE(COUNTAX(Downloads, Downloads[ID Question]),CROSSFILTER ( 'Table'[ID Question], Questions[ID Question], BOTH ))
amt2 = 
var t = CALCULATETABLE(
GENERATE (
    SUMMARIZE ( Questions, Disciplines[Name] ),
    CALCULATETABLE (
        TOPN (
            3,
            CALCULATETABLE(VALUES ( 'Table'[ID Question] )), [questions_loaded] )
        ,
        CROSSFILTER ( 'Table'[ID Question], Questions[ID Question], BOTH )
    )
), ALLSELECTED() )
return
CALCULATE( 1* (NOT ISEMPTY( Questions)), KEEPFILTERS(t))

 

The first measure is for the table and the second one is for the table filter:

ERD_1-1694066211376.png

 

 

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

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

View solution in original post

7 REPLIES 7
ERD
Community Champion
Community Champion

@LuisLOCapelari , this article should help: https://www.sqlbi.com/articles/filtering-the-top-3-products-for-each-category-in-power-bi/

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

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

I already tried to follow the example of this article, but it did not work. It works in parts, when there is only the question and the id of the course it works, but when putting the name of the course (which comes from another table), or putting information from any other table, the rank stops working.

One of the problems you have in your model is many-to-many relationship, which is not recommended.

I've used the second approach from the article along with a bridge table:

ERD_0-1694066097983.png

questions_loaded = CALCULATE(COUNTAX(Downloads, Downloads[ID Question]),CROSSFILTER ( 'Table'[ID Question], Questions[ID Question], BOTH ))
amt2 = 
var t = CALCULATETABLE(
GENERATE (
    SUMMARIZE ( Questions, Disciplines[Name] ),
    CALCULATETABLE (
        TOPN (
            3,
            CALCULATETABLE(VALUES ( 'Table'[ID Question] )), [questions_loaded] )
        ,
        CROSSFILTER ( 'Table'[ID Question], Questions[ID Question], BOTH )
    )
), ALLSELECTED() )
return
CALCULATE( 1* (NOT ISEMPTY( Questions)), KEEPFILTERS(t))

 

The first measure is for the table and the second one is for the table filter:

ERD_1-1694066211376.png

 

 

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

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

Thanks a lot for your help, it worked.

ERD
Community Champion
Community Champion

@LuisLOCapelari , are you sure you've attached the right file? The data there doesn't correspond to the description..

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

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

I'm sorry, it really was the wrong link. I already changed it to the correct link

PS: The questions have classifications, allowing the same question to be classified for different disciplines

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.