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
magtuto
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

 

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
CheenuSing
Community Champion
Community Champion

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

 

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.

Framet
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

Greg_Deckler
Super User
Super User

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...


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 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