Solved

Using Countof with BY


Userlevel 2
Badge +1
  • Seasoned Pigmenteer
  • 22 replies

Dear Community,

 

I have the following metric:

BillsMonths.'MRR SS'[BY:BillsMonths.Mail,BillsMonths.Month]

 

 

 

I am trying to create another metric based on the previous metric, with the following formula:

COUNTOF('Billsmonth Spread')[BY SUM: 'Data Hub'::BillsMonths.Month]

 

And I get the next error in the playground formula

And if I create the metric with the dimension I get the same number for every month

Would you please point me in the right direction?

 

Thank you very much in advance!

Jose

icon

Best answer by francois 10 August 2022, 16:29

View original

10 replies

Userlevel 6
Badge +11

Hi Jose,

If you run COUNTOF in the playground you’ll see it removes the month dimension.

You can do 'Billsmonth Spread'[BY count: Month] to get what you want

Userlevel 6
Badge +14

Hi Jose,
COUNTOF will remove all dimensions. If you want to count cells, you can however use BY COUNT to get to your result.

Hope this helps!

Userlevel 2
Badge +1

That worked well with the [BY count: Month] !  Thanks!  

If I want to do a formula that counts the previous months also? like I want to count how many are not blank but where blank in the previous 3 months, to identify new users.

On excel would be an easy formula like this =COUNTIFS(CK$36:CK$6500;"<>0";CJ$36:CJ$6500;0;CI$36:CI$6500;0)

 

Thank you very much in advance!

Jose

Userlevel 2
Badge +1

I did the following formula and it seemed to work:

 

IF('Billsmonth Spread'[SELECT: 'Data Hub'::Month-3]=0 AND 'Billsmonth Spread'[SELECT: 'Data Hub'::Month-2]=0 AND'Billsmonth Spread'[SELECT: 'Data Hub'::Month-1]=0 AND 'Billsmonth Spread'[SELECT: 'Data Hub'::Month]<>0,1,0)

Userlevel 6
Badge +14

Hi Jose,
You’re on the right track!

Here’s what I’ve built based on your formula. It’s different metrics but the logic should be the same.

The MOVINGSUM helps get the added value for last three months, so a blank value should mean a blank value on each of the last three months.

Please also note that IF does not need a third argument, you can just leave it blank which will be more efficient.

Userlevel 2
Badge +1

Hello Francois;

It doesnt work well the formula of ISDEFINED because I  0s instead of blanks.

I tried adapting it but it did not work:

IF(ISBLANK(MOVINGSUM('Billsmonth Spread',3)[SELECT:'Data Hub'::Month-1])AND 'Billsmonth Spread'[SELECT:'Data Hub'::Month]<>0;1)

 

I got Syntax error: cannot validate formula

Userlevel 6
Badge +14

I don’t get why you added a SELECT in that last part - it’s probably the source of the error.

I think your formula should be

IF(MOVINGSUM(ABS('Billsmonth Spread'),3)[SELECT:'Data Hub'::Month-1] = 0 AND 'Billsmonth Spread' > 0;1)

I’ve added ABS to make sure two consecutive values don’t cancel out.

Userlevel 2
Badge +1

Hi Francois!

 

I tried doing the formula as you suggested, but I still get Syntax error: cannot validate formula

 

Thank you!!

Userlevel 6
Badge +14

I didn’t read the formula completely… You have a semicolon instead of just a comma.
IF(MOVINGSUM(ABS('Billsmonth Spread'),3)[SELECT:'Data Hub'::Month-1] = 0 AND 'Billsmonth Spread' > 0;1)

IF(MOVINGSUM(ABS('Billsmonth Spread'),3)[SELECT:'Data Hub'::Month-1] = 0 AND 'Billsmonth Spread' > 0,1)

Userlevel 2
Badge +1

Thank you Francois!  It worked well.

Reply