topic Re: Need to help in Counting countries separated by commas excluding empty row in DAX Commands and Tips

Need to help in Counting countries separated by commas excluding empty row

aishaakhter18 — Mon, 21 Aug 2023 18:00:00 GMT

Hi I am trying to create a calculation for the col above, first i tried below option

Option 1

Countires Count updated = LEN('PID & HVT Metrics Tracker'[3 Col Countries]) - LEN(SUBSTITUTE('PID & HVT Metrics Tracker'[3 Col Countries],",",""))+1

Then use this option in power query

+List.NonNullCount(Text.Split([3 Col Countries],","))

but both are giving me same result and count the the empty row too as you can see in the attached screen, how should i fix it?

Thanks

Re: Need to help in Counting countries separated by commas excluding empty row

tamerj1 — Mon, 21 Aug 2023 18:56:01 GMT

Hi aishaakhter18

please try

Countires Count =
PATHLENGTH (
SUBSTITUTE ( 'PID & HVT Metrics Tracker'[3 Col Countries], ",", "|" )
)

Re: Need to help in Counting countries separated by commas excluding empty row

aishaakhter18 — Mon, 21 Aug 2023 19:01:03 GMT

Thanks for getting back but still counting the empty row, 😞

Re: Need to help in Counting countries separated by commas excluding empty row

tamerj1 — Mon, 21 Aug 2023 21:39:26 GMT

aishaakhter18

Please try

Countires Count =
PATHLENGTH (
SUBSTITUTE (
IF (
'PID & HVT Metrics Tracker'[3 Col Countries] = "",
BLANK (),
'PID & HVT Metrics Tracker'[3 Col Countries]
),
",",
"|"
)
)

Re: Need to help in Counting countries separated by commas excluding empty row

aishaakhter18 — Tue, 22 Aug 2023 01:19:13 GMT

Thanks alot , it worked

Re: Need to help in Counting countries separated by commas excluding empty row

aishaakhter18 — Thu, 19 Oct 2023 16:42:33 GMT

Thanks it worked, greatly appreciated