Need help with DAX

[Yadidya]

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

[v-nuoc-msft · Accepted solution]

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.

[ToddChitt]

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

[Yadidya]

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

[Yadidya]

@ToddChitt my data source is from this link https://www.kaggle.com/datasets/rohanrao/formula-1-world-championship-1950-2020. The CHampion measure is Champion = CALCULATE(FIRSTNONBLANK(drivers[surname],1), TOPN(1, SUMMARIZE(results, drivers[surname], races[year], "Total_Points", [Total_Points]), [Total_Points], DESC)).

[ToddChitt]

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]

@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]

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?