Forum Discussion

rohitbinnani's avatar
rohitbinnani
Icon for Advocate III rankAdvocate III
9 years ago

DAX query for Games Winning %

In power BI i want to filter by Team A's Bangalore and want Winning % of Bangalore as Team A. i.e. if i Filter with Bangalore from Team A i want (Winner/Team A)*100 which shows me the winning % of Bangalore also it should match the exact figure if i filter it with Year. This Data set is just an Example of of my Question, my actual data set is sensitive and very huge so can't post it here. Also Team A column belongs from a different table and Winner Belongs from a different table. So how can i create a Dax Query for this ?

Match Against Mizoram

 

Thanks and Regards,

Rohit Binnani

 

Need Help
Show and Tell
Tutorial Requests

2 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi rohitbinnani,

     

    Since column [Team A] belongs from a different table and [Winner] comes from another table, you should first combine them into a single table based on specific relationship. 

     

    As I don't know what relationship is between the original two tables, suppose the combination table looks like above you posted. And there are two slicers in your report, one is for [Team A], another is for [Year]. In that case, please create measures like:

    CountSelectedWinner =
CALCULATE (
    COUNTA ( 'Game Winning'[Winner] ),
    FILTER (
        'Game Winning',
        'Game Winning'[Winner] = LASTNONBLANK ( 'Game Winning'[Team A], 1 )
    )
)

    CountTeamA =
    CALCULATE (
     COUNTA ( 'Game Winning'[Team A] ),
     FILTER (
     'Game Winning',
     'Game Winning'[Team A] = LASTNONBLANK ( 'Game Winning'[Team A], 1 )
     )
    )

    Winning% = [CountSelectedWinner]/[CountTeamA]

     

    If I have something misunderstood, please provide more description about your requirement.

     

    Thanks,
    Yuliana Gu

    • rohitbinnani's avatar
      rohitbinnani
      Icon for Advocate III rankAdvocate III
      9 years ago

      Hi v-yulgu-msft,

       

      i tried it with different table. what if my filter of Team A is in and connected in this table as per a primary key. we have team A in this table itself but i have another dimension from which i am bringing my Team A as filter just because that is also connected to some more tables to filter them too. can you help me in this ?

      the script you provided works perfect if they belong from same table. !!!

       

      Regards,

      Rohit Binnani.