calculate the count and measure as filter

[Anonymous]

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. 

calculate function does not allow to pass expression like below.

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

sample data:

Match_Id	Innings_Id	Over_Id	Ball_Id	Team_Batting_Id	Team_Bowling_Id	Striker_Id	Striker_Batting_Position	Non_Striker_Id	Bowler_Id	Batsman_Scored
335987	1	2	2	1	2	2	2	1	15	4
335987	1	2	3	1	2	2	2	1	15	4
335987	1	2	4	1	2	2	2	1	15	6
335987	1	2	5	1	2	2	2	1	15	4
335987	1	3	4	1	2	2	2	1	14	4
335987	1	3	5	1	2	2	2	1	14	1
335987	1	4	2	1	2	2	2	1	13	6
335987	1	4	4	1	2	1	1	2	13	4
335987	1	4	6	1	2	1	1	2	13	1
335987	1	4	7	1	2	2	2	1	13	6
335987	1	5	1	1	2	1	1	2	14	4
335987	1	5	2	1	2	1	1	2	14	1
335987	1	5	3	1	2	2	2	1	14	4
335987	1	5	5	1	2	2	2	1	14	1
335987	1	6	1	1	2	2	2	1	15	1
335987	1	7	1	1	2	2	2	3	13	1
335987	1	7	2	1	2	3	3	2	13	1
335987	1	7	3	1	2	2	2	3	13	1
335987	1	7	4	1	2	3	3	2	13	2

[AlexisOlson · Accepted solution]

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]

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

[Ashish_Mathur]

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]

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

[Ashish_Mathur]

Hi,

This should work

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