cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Skotzmun
Frequent Visitor

COUNTX but including a calculated column that is TRUE or FALSE

I asked a question last month about counting values based on dates, which was wonderfully answered by @v-robertq-msft. With a dataset representing the last time a series of media-playing "Faces" connected back to control I wanted to know how many had connected within 48 hours of the latest date in the "last connected" field. I've got this  sorted and even been able to add extra filters into the count.

 

This is a link to a .PBIX containing example data - https://we.tl/t-iBoqNLm4pB

 

The original stuff is in the table (Players), and Robert's solution is powering the upper gauge visual on display with the Measure Online48HoursAll

 

However, I now have a second table called (SpecialPlayers) that lists a subset of the records in the first (for example, this could be Faces operating in a certain region). I've related these two using [FaceID] and then created a new column [IsSpecial] in (Players) that returns TRUE if that FaceID is in (SpecialPlayers), or FALSE otherwise. 

 

I now want to do the same count into the lower gauge, but only consider those players where [IsSpecial] is TRUE. I can filter the total count easily enough by using [IsSpecial] as a filter on the visual, but I cannot figure out the way to use [IsSpecial] to modify the new Measure Online48HoursSpecial to do the same.

 

I'm guessing it's partially to do with COUNTX not being for logical counts, but I've not been able to include a calculated column in the ...Special Measure even when I added a calculated number value column that was the first digit of the timestamp.

 

Any assistance, as ever, hugely appreciated.

 

Cheers

 

Skotzmun

 

 

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

The problem is that your counting measure removes all filters on the Players table with ALL.

 

Instead of ALL ( Players ), try using ALLSELECTED ( Players ) or simply Players depending on what your goal is.

View solution in original post

2 REPLIES 2
AlexisOlson
Super User
Super User

The problem is that your counting measure removes all filters on the Players table with ALL.

 

Instead of ALL ( Players ), try using ALLSELECTED ( Players ) or simply Players depending on what your goal is.

View solution in original post

Hi Alexis

 

Brilliant - that works nicely. Thanks. 🙂

 

So to make sure I understand my error properly, am I right in saying:

ALL(Players) causes the Measure to completely bypass any filters set for the visual / page / all pages

ALLSELECTED(Players) will take into account any filters set on the visual / page / pages

 

If so, this looks like a great basis for my next expansion, which is to associate the REGION to each Player from the (SpecialPlayers) relationship and then filter for online in that region.

 

EDIT: I just tried it and it worked like a charm for my regional switching... thanks even more than before. 😄

 

Thanks again for the quick and concise response.

 

KR

 

Skotzmun

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.