How to perform self join with group by and where contions

[Anonymous]

Hi Community,

I have a table with below data, Trying to create a mesure for count of'PlayerName' who 'Participated Game' in crickte with 'Result' as null and also having 'Participated Game ' in Football for 2 times with 'date' notnull .
so that i can show this count on a bar chart against months.

Player Name	Participated Game	Result	date
a	cricket	Incomplete	1/23/2019
a	cricket	Complete	1/25/2019
b	cricket	Incomplete	1/26/2019
b	cricket	Complete	1/28/2019
c	cricket	Incomplete	1/18/2019
c	cricket	null
d	Hockey	Incomplete	1/18/2019
d	Hockey	Complete	1/22/2019
a	Football	null
c	Football	null
c	Football	complete	1/11/2019
c	Football	Complete	1/13/2019
d	Football	Complete	1/23/2019

sample Query looks like (Postgresql):
select count(*) from (
select a.PlayerName
,FootballCount
from PlayerTable a inner  join
(select PlayerName,count(*) as FootballCount from PlayerTable where ParticipatedGame='Football'
and Date notnull
group by PlayerName
having count(*)=2
)b
on a.PlayerName=b.PlayerName
where ParticipatedGame='cricket'
and Result is null
)c

From above given data the the measure count would be of 1 (C with cricket and 2 times Football)

Not able to fix above logic in DAX.  Thank you in advance for your suggestions/answers!

[Ashish_Mathur · Accepted solution]

Hi,

This measure works

=COUNTROWS(FILTER(SUMMARIZE(VALUES(Data[Player Name]),Data[Player Name],"ABCD",CALCULATE(COUNTROWS(Data),Data[Result]="null",Data[Participated Game]="Cricket"),"EFGH",CALCULATE(COUNTROWS(Data),Data[Result]<>"null",Data[Participated Game]="Football")),[ABCD]=1&&[EFGH]=2))

Hope this helps.

[Anonymous]

---

@Ashish_Mathur wrote:

Hi,

This measure works

=COUNTROWS(FILTER(SUMMARIZE(VALUES(Data[Player Name]),Data[Player Name],"ABCD",CALCULATE(COUNTROWS(Data),Data[Result]="null",Data[Participated Game]="Cricket"),"EFGH",CALCULATE(COUNTROWS(Data),Data[Result]<>"null",Data[Participated Game]="Football")),[ABCD]=1&&[EFGH]=2))

Hope this helps.

---

Hi Ashish_Mathur ,

Thank you for reply. In this measure you have taken plyaer name "ABCD" exclusively but in my case  I have 'n' number of player names then  how could it be achieved.

Thanks in advance 

[Ashish_Mathur]

Hi,

ABCD is just a column title of a column you are creating in a virtual table.  Rename that to what else you deem appropriate.

[ElenaN]

Hello,

This should work

Measure_ = 
VAR FootballCount = CALCULATE(COUNTROWS(Table1), FILTER(Table1,Table1[Participated Game] = "Football" && Table1[date] <> BLANK()))
VAR CricketNullCount = CALCULATE(COUNTROWS(Table1), FILTER(Table1, Table1[Participated Game] = "Cricket" && Table1[Result] = "null"))
RETURN
CALCULATE(DISTINCTCOUNT(Table1[Player Name]), FILTER(Table1, FootballCount > 0 && CricketNullCount > 0))

Regards,

ElenaN

[Anonymous]

Hi ElenaN

When I built this measure i'm getting below error , I think PBI is not taking Variables in filter condition that again not sure. Can you please help on this by doing any other possible way...

"A function 'FILTER' has been used in a True/False expression that is used as a table filter expression. This is not allowed."