cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Mhetre123 Regular Visitor
Regular Visitor

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

Accepted Solutions
AlexisOlson Member
Member

Re: calculate the count and measure as filter

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))
5 REPLIES 5
Super User
Super User

Re: calculate the count and measure as filter

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.

AlexisOlson Member
Member

Re: calculate the count and measure as filter

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))
AlexisOlson Member
Member

Re: calculate the count and measure as filter

@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
    )
)
Super User
Super User

Re: calculate the count and measure as filter

Hi,

 

This should work

 

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

Mhetre123 Regular Visitor
Regular Visitor

Re: calculate the count and measure as filter

@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