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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
amitgajkal2007
Frequent Visitor

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

team1team2winner
aca
abb
bab
bcc
caa
cbb
daa
add

 

Ouput data:

Teammatcheswins
a63
b43
c41
d21
1 ACCEPTED SOLUTION
Ritaf1983
Super User
Super User

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

Ritaf1983_0-1713521275122.pngRitaf1983_1-1713521311072.png

Then you will get the table as in the picture :

Ritaf1983_2-1713521371773.png

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 :
Ritaf1983_3-1713521541163.png

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

 

 

View solution in original post

6 REPLIES 6
sanalytics
Solution Supplier
Solution Supplier

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

sanalytics_0-1713526851016.png

 

Regards

sanalytics

 

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

Ritaf1983
Super User
Super User

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

Ritaf1983_0-1713521275122.pngRitaf1983_1-1713521311072.png

Then you will get the table as in the picture :

Ritaf1983_2-1713521371773.png

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 :
Ritaf1983_3-1713521541163.png

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

 

 

Thats good one....i spent hours to figure it out using DAX. Prbolem with this is that it forms many to many relationship that is difficult to handle
Is there a solution using DAX? (Just Curious)

Hi @amitgajkal2007 
Happy to help 🙂 Why many to many,
it is just 1 table..
According to DAX, I see that @sanalytics gave a solution.

Unpivoting creates a havoc on other reports. the slicers simply stop to work and doesnt filter. 

 

hence i was asking for DAX approach. I have 4 tables, 20+ columns and 20k+ rows. 

 

Thanks

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors