topic Re: Filter Problem in DAX Commands and Tips

Filter Problem

sdm2211 — Mon, 20 Jan 2025 16:36:22 GMT

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

Re: Filter Problem

speedramps — Mon, 20 Jan 2025 17:16:53 GMT

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 😀

Re: Filter Problem

sdm2211 — Mon, 20 Jan 2025 18:36:04 GMT

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.

Re: Filter Problem

Anonymous — Wed, 22 Jan 2025 03:24:59 GMT

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.

Re: Filter Problem

sdm2211 — Wed, 22 Jan 2025 10:14:10 GMT

hi Anonymous

Here is the sample file for your use.

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

Re: Filter Problem

Anonymous — Thu, 23 Jan 2025 02:31:41 GMT

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.

Re: Filter Problem

sdm2211 — Thu, 23 Jan 2025 04:25:12 GMT

Hi Anonymous Sorry for inconvenience.

can you please re-check now

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

Re: Filter Problem

Poojara_D12 — Thu, 23 Jan 2025 06:16:25 GMT

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.

Re: Filter Problem

Anonymous — Thu, 23 Jan 2025 06:17:33 GMT

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.