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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

calculate the count and measure as filter

Hi,

I have measure which calculates the sum of values in Batsman_Scored column. 

Score = SUM(Ball_by_Ball[Batsman_Scored])

 

 

4s = CALCULATE(COUNT(Ball_by_Ball[Batsman_Scored]), Ball_by_Ball[Batsman_Scored] = 4)
6s = CALCULATE(COUNT(Ball_by_Ball[Batsman_Scored]), Ball_by_Ball[Batsman_Scored] = 6)

 

 

I want to DAX codes which calculate count of Match IDs where Score of Stiker Id is 

  1. 50s = between 50 to 99
  2. 100s = greater than 99

I want visual that look like below table. 

score-card.JPG

 

calculate function does not allow to pass expression like below.

 

100s = CALCULATE(COUNT(Ball_by_Ball[Match_Id]), [Score] > 99)

 

 

 

sample data:

 

Match_IdInnings_IdOver_IdBall_IdTeam_Batting_IdTeam_Bowling_IdStriker_IdStriker_Batting_PositionNon_Striker_IdBowler_IdBatsman_Scored
33598712212221154
33598712312221154
33598712412221156
33598712512221154
33598713412221144
33598713512221141
33598714212221136
33598714412112134
33598714612112131
33598714712221136
33598715112112144
33598715212112141
33598715312221144
33598715512221141
33598716112221151
33598717112223131
33598717212332131
33598717312223131
33598717412332132
1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

The CALCULATE function only allow simple filters to be passed in as arguments. If you want more complex expressions, you typically use the construction

 

CALCULATE(<...>, FILTER(<Table>, <Conditions>))

In this case though, I think it would be simpler to use a different approach along these lines:

 

100s = SUMX(DISTINCT(Ball_by_Ball[Match_Id]), IF([Score] > 99, 1, 0))

View solution in original post

5 REPLIES 5
AlexisOlson
Super User
Super User

The CALCULATE function only allow simple filters to be passed in as arguments. If you want more complex expressions, you typically use the construction

 

CALCULATE(<...>, FILTER(<Table>, <Conditions>))

In this case though, I think it would be simpler to use a different approach along these lines:

 

100s = SUMX(DISTINCT(Ball_by_Ball[Match_Id]), IF([Score] > 99, 1, 0))
Anonymous
Not applicable

@AlexisOlsonand @Ashish_Mathur guys Thank you for prompt repsonse

 

100 Sumx = SUMX(DISTINCT(Ball_by_Ball[Match_Id]), IF([Score] > 99, 1)) 
50s sumx = SUMX(DISTINCT(Ball_by_Ball[Match_Id]), IF( [Score] > 49 && [Score]<=99, 1)) 

These are measures working perfectly.

sumx.JPG

 

 

 

Ashish_Mathur
Super User
Super User

Hi,

 

Try this

 

100s = CALCULATE(DISTINCTCOUNT(Ball_by_Ball[Match_Id]),FILTER(Ball_by_Ball,[Score]>99))

99s = CALCULATE(DISTINCTCOUNT(Ball_by_Ball[Match_Id]),FILTER(Ball_by_Ball,[Score]>50&&[Score]<=99))

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur, that doesn't work because of the way [Score] is evaluated within the FILTER function's row context.

 

You can fix it as follows, but it's rather messy:

 

 

100s =
CALCULATE (
    DISTINCTCOUNT ( Ball_by_Ball[Match_Id] ),
    FILTER (
        Ball_by_Ball,
        CALCULATE ( [Score], ALLEXCEPT ( Ball_by_Ball, Ball_by_Ball[Striker_Id] ) ) > 99
    )
)

Hi,

 

This should work

 

100s = COUNTROWS(FILTER(VALUES(Ball_by_Ball[Striker_ID]),[Score]>99))


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.