cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Dax Help

If I had a table that had the following columns:

 

| Name |  Basketball |  Tennis | Hockey |

| John   |   3              |  3          |  2          |

| Chris  |   3              |  3          |  3          |

| Pedro | 5                | 5           | 4           |

 

Say I only want to count rows (Name) that have scores higher than 3 and above for the all 3 columns (i.e. Basketball, Tennis and Hockey).

 

In this case out output would be 2 (counting Chris and Pedro), whats the best DAX equation for this?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

 

CheenuSing

 

Actually I figured it out as soon as i posted it and its similar to what you have posted.  But I ended up using the measure as per below:

 

All Categories 3 & Above = CALCULATE(
COUNTROWS(Table),
Table[Column1]> 2, Table[Column2]> 2, Table[Column3]>)

 

Thanks to everyone for thier contibution.

View solution in original post

4 REPLIES 4
Highlighted
Super User IV
Super User IV

Huh, I just ran into a similar situation last night and I ended up brute forcing it. I actually had 16 columns that I had to deal with. What I did was create measures for each column and then a calculated column that summed them all up. So, in your case you might have:

 

MetricBasketBallGT3 = IF(SUM([Basketball])>3,1,0)

MetricTennisGT3 = IF(SUM([Tennis])>3,1,0)

MetricHockeyGT3 = IF(SUM([Hockey])>3,1,0)

MetricAllGT3 = [MetricBasketBallGT3] + [MetricTennisGT3] + [MetricHockeyGT3]
CalculatedColumn = [MetricAllGT3]

You might not need the calculated column unless you wanted to know that number for each individual. I needed it because I was computing an overall average between the 16 columns but I wanted to exclude zeros when computing that average.

 

It was late last night, so perhaps I just didn't have my creative thinking cap on with regard to how to solve this more elegantly...


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Resolver II
Resolver II

Hi,

 

You could do as suggested and it would work. I might consider avoiding too many "IF" statements as it might cause issues with performace in large data sets especially if you go onto to use these measures in other complex measures.

A calculated column might be easiest especially if you want to deal with more than three columns.

CalColumn =

MIN ( MIN ( [Basketball], [Tennis] ), [Hockey] )

 

There may be a more elegant mathmatical way to get the smallest of more than three numbers but I also can't think of it right now.

 

Then your measure would be (assuming one row per person in this case):

CountNamesGrtr3 :=
CALCULATE (
    COUNTROWS ( 'DataTable'), filter('DataTable','DataTable'[CalColumn] >= 3 )
)

You could use COUNTX and build the MIN function into the filter but I would probebly avoid that and take the additional overhead of the calculated column.

Hope this helps.

 

Thomas

Highlighted
Super User I
Super User I

@magtuto

 

Try the expression 

 

TotRows = Calculate ( CountRows(YourTable),
                                   Filter( ALL(YourTable),
                                                   (  YourTable[ Hockey ] >=3 &&YourTable[  Basketball ] >= 3 && YourTable[  Tennis ]
                                                   )
                                            )
                                   )

 

If this works please accept this as solution and also give KUDOS.

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Highlighted

 

CheenuSing

 

Actually I figured it out as soon as i posted it and its similar to what you have posted.  But I ended up using the measure as per below:

 

All Categories 3 & Above = CALCULATE(
COUNTROWS(Table),
Table[Column1]> 2, Table[Column2]> 2, Table[Column3]>)

 

Thanks to everyone for thier contibution.

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors