Filter Problem

[sdm2211]

Dear all,

I am having following table with the mentioned data

Batsman	Wicket_Type	Player_Dismissed
KS Bharat	caught	KS Bharat
KS Bharat	bowled	KS Bharat
KS Bharat	stumped	KS Bharat
KS Bharat	caught	KS Bharat
KS Bharat	run out	KS Bharat
KS Bharat	run out	V Kohli

above table shows Batsman KS Bharat is dismissed/out 5 times (caught-2, bowled-1, stumped-1, run out-1) hence ideally it should show me 5 times. however it showing me 6 times out (including that of V Kohli as well)

I used DAX formula as below

M Test =

Calculate(COUNTA(Deliveries[Player_Dismissed]),Filter(Deliveries,AND(Deliveries[Player_dismissed]<>"",Deliveries[Wicket_Type]<>"run out")))

however it is filtering 1 entry of "run out" for KS Bharat and returning count as 4 instead of 5. 

How can i achieve the correct result

[Poojara_D12]

Hi @sdm2211 

Can you please try this dax:

M Test =
CALCULATE(
    COUNTA(Deliveries[Player_Dismissed]),
    FILTER(
        Deliveries,
        Deliveries[Player_Dismissed] = Deliveries[Batsman] &&
        Deliveries[Player_Dismissed] <> "" &&
        Deliveries[Wicket_Type] <> ""
    )
)

• Only rows where Player_Dismissed matches the Batsman are counted.
• "Run out" is included if it applies to the same batsman.

[v-xianjtan-msft]

Hi @sdm2211 

I'm sorry I can't fully understand your question, but as speedramps said, based on the table data you posted, it looks like your DAX returns result 4 correctly.
What is the 'Batsman_Runs' column mentioned in your latest reply?
Could you please provide sample data that completely covers your question and your expected results?

Please refer to the link below:

How to Get Your Question Answered Quickly - Microsoft Fabric Community

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sdm2211]

hi @v-xianjtan-msft 

Here is the sample file for your use.

https://drive.google.com/file/d/1CTCumgGgksidcWrvQ8afjoqDj4LCnNH_/view?usp=drive_link 

[v-xianjtan-msft]

Hi @sdm2211 

I don't have access to this file.

Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sdm2211]

Hi @v-xianjtan-msft Sorry for inconvenience.

can you please re-check now

https://drive.google.com/file/d/1CTCumgGgksidcWrvQ8afjoqDj4LCnNH_/view?usp=sharing 

[v-xianjtan-msft]

Hi @sdm2211 

If I understand you correctly, you are trying to count the number of times each batsman dismissed. Based on your sample data, you can create the metric using the following DAX:

Dismissed_times = 
CALCULATE(
    COUNTROWS(Test),
    FILTER(
        Test,
        Test[Player_Dismissed] = Test[Batsman]
    )
)

In your measure, you filtered using AND(TEST[Player_Dismissed] = “”, TEST[Wicket_Type] = “run out”) and got result 3 as it should be.
You can verify this by manually filtering your data in Power BI:
1. Filtered by TEST[Player_Dismissed] = “”

2. Filtered by TEST[Wicket_Type] = “run out”

The final result of COUNTA(Test[Player_Dismissed]) is of course 3.

Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[speedramps]

You have used a FILTER .

The last 2 records dont have a blank playe_dismissed name

and the Type is not run_out.  So the answer is 4.

If you explain what you want rather than post your buggy DAX and expect us to fathom what you want, then we can try help. Thanks 😀

[sdm2211]

Sorry for misleading information provided earlier. I am very new to PowerBI & DAX hence might not be able to construct my question properly. I am not able to attach sample PowerBI here  to explain my case

What I was saying is;

Column Batsman have 5 entries of KS Bharat

Column Batsman_Runs have sum of 10 for all those 5 entries

Column Player_Dismissed have 4 entries of KS Bharat and 1 entry of V Kohli

Column Wicket_Type have 4 entries for KS Bharat (2-caught, 1-bowled, 1-run out) and 1 entry for V Kohli (1-run out)

now as i earlier mentioned I used DAX formula

M Dismissed_Times=
Calculate(COUNTA(Test[Player_Dismissed]), FILTER(TEST, AND(TEST[Player_Dismissed]<>"",TEST[Wicket_Type]<>"run out")

which I know it is giving me wrong result.

I want Dismissed_Times should show 4 in the matrix, where it is showing 3 by using above wrong DAX formula.