Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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.
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.
@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
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.
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
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."
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |