Table totals are not counting as expected in visual

[sedric1]

Hi I have a single table full of user IDs. Some of them user IDs are duplicated, some have blank values and some have #N/A values (which i was hoping to filter down to a count of distinct user IDs, categorized by the type of user. 

The categorized column for users displays super users vs end users using a new column, and tried to categorize the counts and show the total count (formula below)

User Type = if ( ISBLANK(Users[TMS ID])=FALSE() && Users[TMS ID] <> "#N/A", IF (

ISBLANK('Users'[SU TMS Program])=TRUE(),

"End User",

"Super User"

), "")

First I tried this measure but total counts still werent correct:

User Count = CALCULATE(DISTINCTCOUNTNOBLANK('URA'[TMS ID]),'URA'[TMS ID]<>"#N/A", 'URA'[TMS ID]<>blank() )

After reading some thoughts on other posts, I realized that measures and totals have issues calculating so then I tried just using the power bi options to count distinct + filter out the value from the visuals, but it doesnt seem to apply to the totals.

Also here is a sample of the data:

TMS ID	User Type	SU TMS Program
DLSDJFSVNWEN.KLSDJFSLLY1112	Super User	Training Curriculum Assigned
DLSDJFSLLNWENNT.LNWESldk219	Super User	Training Curriculum Assigned
19922411	Super User	Training Curriculum Assigned
JNTBLSDJFSS.BRldkNDNWE2912	Super User	Training Curriculum Assigned
CRldkNWEGMNWELLSDJFS.SUSldkN27292	Super User	Training Curriculum Assigned
HUGHLSDJFSS.LNWENldk12921	Super User	Training Curriculum Assigned
	Super User	Training Curriculum Assigned
HNTLLNTWldkY.LSDJFSLNWEZldkBLSDJFSTH21	Super User	Training Curriculum Assigned
TMS ID	User Type	SU TMS Program
	End User
#N/A	End User
#N/A	End User
#N/A	End User
11127	End User
11127	End User
1179222	End User

[VijayP · Accepted solution]

@sedric1 

SWITCH(TRUE()

NOT(ISBLANK(TMSID)) || TMSID<>"#N/A"||ISBLANK(TMSPROGRAM),"End USer","Super USer"

Try this function and share your kudoes

[sedric1]

@VijayP  Thanks for the quick reply. I am still learning and therefore not sure where I would enter a function like that. Can you guide to where functions can be defined in power bi? 

[VijayP]

@sedric1 

Create a Calculated Column. Or Create a measure and add that measure in to the Table visual

Please sshare your kudoes

[sedric1]

@VijayP  Thanks a lot! it appears to be working . I will mark this as resolved but 1 thing I am unclear on is- why this works in your custom formula, yet the way I did a custom column did not work originally?

Either way, thanks again!

[VijayP]

@sedric1 

Thanks , Pleasae mark this as Solution !