Forum Discussion

amitgajkal2007's avatar
amitgajkal2007
Frequent Visitor
2 years ago
Solved

Dax query

I have a table named (data) and two columns team1 and team2 want to undersand  1. count number of maches played by each team and  2. count of wins by each team   input data team1 team2 win...
Need Help
  • Ritaf1983's avatar
    Ritaf1983
    2 years ago

    Hi amitgajkal2007 
    The first step is to transform the table to vertical to make the analysis easier.

    You can do it with the "unpivot" option of PQ

    Then you will get the table as in the picture :

    From here you can create 2 simple dax measures :

    1. For matches :

    matches = COUNTROWS('Table')
    2. For wins:
    wins = CALCULATE([matches],SELECTEDVALUE('Table'[Team])='Table'[winner])
    Result :

    pbix is attached, you can follow my steps at pq and dax.

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

     

     

sanalytics's avatar
sanalytics
Super User
2 years ago

Hello amitgajkal2007 

 

As per your question has stated "Dax Query",

Below is the complete DAX code without doing any manipulation in PQ.

 

Step : Create a Calculated Table for Supporting purpose. Below is the code.

 SupportingTable =
UNION(
SELECTCOLUMNS(
'Table',"Teams",'Table'[team1]
),
SELECTCOLUMNS(
'Table',"Teams",'Table'[team2]
) )

 

Step : Use the below code for your output in DAX studio or DAX query view in Power BI desktop

  EVALUATE
 VAR _2 = 
 SELECTCOLUMNS(
 SUMMARIZE(
 UNION(
SUMMARIZECOLUMNS( 'Table'[team1] ),
  SUMMARIZECOLUMNS( 'Table'[team2] 
  )
  ), 'Table'[team1]
 ), "@Teams",'Table'[team1] )
VAR _3 = 
 ADDCOLUMNS(
    _2,"MatchCount", 
    CALCULATE( COUNT( SupportingTable[Teams] ), FILTER( SupportingTable, SupportingTable[Teams] = [@Teams] ) ),
    "Wins",CALCULATE( COUNT('Table'[winner] ), FILTER( 'Table','Table'[winner] = [@Teams] ) )
 ) 
RETURN
_3

 

Below is the screenshot

 

Regards

sanalytics

 

  • amitgajkal2007's avatar
    amitgajkal2007
    Frequent Visitor
    2 years ago

    I am trying to use above (mentioned in DAX query) code in a measure but i am not able to get it. 

    This is a reference table which i have build...actual tbale has 4 table, like 20 columns and realted with each other. 

    I am not able to use unpivot option if i use it then it create havoc in other dashboards (the slicer simply doesnt filter).

    thank you but if you can let me know how i can use it above query in Dax measure then i will use the analogy on actual data. 

     

    Thank you