Forum Discussion

Yadidya's avatar
Yadidya
Regular Visitor
2 years ago
Solved

Need help with DAX

Hello !!!

I am pretty much new to Power BI. I am trying to build a Formula 1 visualization from this dataset:https://www.kaggle.com/datasets/rohanrao/formula-1-world-championship-1950-2020. I have created a measure to show who is the champion with the help of Chat GPT, to be honest. The measure is 

Champion = CALCULATE(FIRSTNONBLANK(drivers[surname],1), TOPN(1, SUMMARIZE(results, drivers[surname], races[year], "Total_Points", [Total_Points]), [Total_Points], DESC)). It shows the champion name and if we use the year as a filter, we can get the list of champions for every year (attachment). Now I want to visualize in a bar chart how many times a driver became a champion. Hamilton 7 times, Schumacher 7, so on ..
 
Kindly help
 
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Yadidya 

     

    ToddChitt Thank you very much for your prompt reply.

     

    I was checking the forum posts and found your post. May I ask if your problem has been solved? If not yet, I have an idea.

     

    If the measure you provided outputs the desired result, then please try it:

     

    Create a measure.

     

     
    count champion = 
var currentChampion = [champion]
RETURN
CALCULATE(
    COUNTROWS('Table'), 
    FILTER(
        ALL('Table'), 
        'Table'[champion] = currentChampion
    )
)

     

    Here is the result.

     

     

    I've attached a simple demo.

     

    Regards,

    Nono Chen

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

     

7 Replies

  • ToddChitt's avatar
    ToddChitt
    Super User
    2 years ago

    You don't need DAX for that. Simply build a  COLUMN chart, add Driver Name (Champion) on the X axis and COUNT(Year) on the Y axis. Note that when you add [Year], because it is numeric, it may want to summarize it. You need to switch the aggregation from SUM to COUNT.

     

    • Yadidya's avatar
      Yadidya
      Regular Visitor
      2 years ago

      ToddChitt When attempting to add the 'Champion' measure to the X-axis of a column chart, it is not being accepted, even when dragging and dropping. 

      • ToddChitt's avatar
        ToddChitt
        Super User
        2 years ago

        Maybe go back and ask Chat GPT what went wrong? 🙂

        Can you show us an example of your source data BEFORE Chat GPT did anything to it?

  • ToddChitt's avatar
    ToddChitt
    Super User
    2 years ago

    Can you just take some screen shots of actual data (not the model view) of the tables involved?

    • Yadidya's avatar
      Yadidya
      Regular Visitor
      2 years ago

      ToddChitt Thanks for the reply. I am attaching the screenshots of the tables.  

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Yadidya 

     

    ToddChitt Thank you very much for your prompt reply.

     

    I was checking the forum posts and found your post. May I ask if your problem has been solved? If not yet, I have an idea.

     

    If the measure you provided outputs the desired result, then please try it:

     

    Create a measure.

     

     
    count champion = 
var currentChampion = [champion]
RETURN
CALCULATE(
    COUNTROWS('Table'), 
    FILTER(
        ALL('Table'), 
        'Table'[champion] = currentChampion
    )
)

     

    Here is the result.

     

     

    I've attached a simple demo.

     

    Regards,

    Nono Chen

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