Analyse repeat clients in different financial years

[Ashish Mathur]

Hi,

I am trying to analyse repeat clients data for different financial years. My question is very clearly explained in the Question worksheet of the file at the following link - http://sdrv.ms/10giNL6.

At the very outset, I must mention that Gerhard Brueckl has answered my question at this link - Analysis of repeat clients

However, I am trying to identify the error in my calculated field formula. This is purely for learning purposes.

I would really appreciate your help.

Thank you.

 

[MD610]

Ashish,

I am not able to view the Data Model because I am getting a message that it is damaged for whatever reason.

However, I can see your question worksheet. The error message you see, "A Table of multiple values was supplied where a single value was expected", is usually the result of using VALUES() in your measure and then placing that measure in a pivot context where VALUES() returns more than a single value. Technically, VALUES() always returns a single column table of unique values. When you use it in some type of boolean test, you need it to return a single value. This is logically not incorrect and therefore you don't get an error until you place the measure in a pivot context it can't handle. If your pivot rows contained the same column that was inside your VALUES() without any additional columns used in rows above it (no subtotals) then you probably wouldn't be seeing an error.

As soon as you have any type of subtotal or aggregation on the column being referenced in VALUES(), it returns more than one value and you get an error. Hope that makes sense as it can be a little confusing to wrap your head around at first.

There are ways to deal with it in the DAX but without being able to see your actual measure formula, I'm not sure what the best approach would be.

 

[Ashish Mathur]

Hi,

Thank you for looking into my question. I a able to download the file and view the formula without any problem. Anyways, I will post my calculated field formula here:

1. In the column area of the Pivot Table, I first dragged financial years
2. I wrote a calculated field formula titled as [Clients visited] =CALCULATE(DISTINCTCOUNT([Organised by]))
3. I wrote a calculated field formula titled as [two years prior] =edate(ENDOFYEAR(Calendar1[Date],"3-31"),-24)
4. I wrote a calculated field formula titled as [Current year] =edate(ENDOFYEAR(Calendar1[Date],"3-31"),-12)
5. Finally I wrote a calculated field formula titled as [Clients transacted with for two consecutive years]. The formula for this is

=COUNTROWS(FILTER(SUMMARIZE(Feedback,[Organised by],"Count",COUNTAX(FILTER(generate(SUMMARIZE(Calendar1,Calendar1[Fiscal Year]),SUMMARIZE(Feedback,[Organised by])),DATESBETWEEN(Calendar1[Date],[Two years prior],[Current year])),[clients visited])),[count]=2))

The problem is with this calculated field formula.

Hope this information helps.

 

[MD610]

Ashish,

Basically, what I said about VALUES() applies to any function that returns a table. So, at quick glance, I suspect one of your SUMMARIZE() is the culprit.

That's a pretty complex measure and as such will take some effort to troubleshoot. When I get some free time, I'll take a closer look.

 

[Ashish Mathur]

Hi,

Sure. Will wait for your revert.

Thank you.

 

[MD610]

Ashish,

I had a chance to look at this better last night and quickly during lunch today.

It looks like your specific error message was arising from the DATESBETWEEN(). I think the date measures you were using as parameters in DATESBETWEEN() were returning multiple dates when using Fiscal Years as rows. As I mentioned above when discussing VALUES() you probably wouldn't have seen the errors if for example the actual dates were used as Rows instead.

All that being said, I don't know that some of the other parts of your measure would have returned the results you needed. In particular the GENERATE() which was returning a table of all clients with all fiscal years for every client.

I took a slightly different route but believe I have come up with a measure that returns what you want for clients with visits in 2 consecutive years:

=IF(HASONEVALUE(Calendar1[Fiscal Year]),
                            CALCULATE(
                                          COUNTX(
                                                     FILTER(
                                                              ADDCOLUMNS(
                                                                            SUMMARIZE(
                                                                                           Feedback,
                                                                                           Feedback[Organised By], 
                                                                                           Calendar1[Fiscal Year]
                                                                             ),
                                                                            "YearCnt",
                                                                            CALCULATE(
                                                                                        DISTINCTCOUNT(Calendar1[Fiscal Year]),
                                                                                        FILTER(
                                                                                                   Feedback, 
                                                                                                   Feedback[Organised By] = EARLIER(Feedback[Organised By])
                                                                                        )
                                                                            )
                                                                ), 
                                                                [YearCnt]>1
                                                          ),
                                                         [Clients Visited]
                                          ), 
                                          ALL(Calendar1), 
                                          FILTER(ALL(Calendar1),
                                                    Calendar1[Fiscal Year] <= VALUES(Calendar1[Fiscal Year]) &&
                                                    Calendar1[Fiscal Year] > VALUES(Calendar1[Fiscal Year])-2)
                                )/2,
                BLANK()
)

I did my best to stick to your naming conventions and don't think I missed anything.

I get the following results:

	Column Labels
Values	2007	2008	2009	2010	2011	2012	2013	Grand Total
Clients visited	1	12	22	33	36	24	1	86
2ConsecutiveYears			1	9	14	12

<tbody>
</tbody>

Let me know if this works for you.

 

[Ashish Mathur]

Hi,

Thank you for replying. I am trying to identify the error in my solution. The calculated field formula below reveals no errors but when I try to drag it inside the Pivot Table, I get an error message saying that "A Table of multiple values was supplied where a single value was expected"

=COUNTROWS(FILTER(SUMMARIZE(Feedback,[Organised by],"Count",COUNTAX(CALCULATETABLE(generate(SUMMARIZE(Calendar1,Calendar1[Fiscal Year]),SUMMARIZE(Feedback,[Organised by])),FILTER(all(Calendar1),Calendar1[Fiscal Year]<=VALUES(Calendar1[Fiscal Year])&&Calendar1[Fiscal Year]>=VALUES(Calendar1[Fiscal Year])-1)),[clients visited])),[count]=2))

Can you please help me identify the error.

 

[Ashish Mathur]

Hi,

Thank you for replying. I am trying to identify the error in my solution. The calculated field formula below reveals no errors but when I try to drag it inside the Pivot Table, I get an error message saying that "A Table of multiple values was supplied where a single value was expected"

=COUNTROWS(FILTER(SUMMARIZE(Feedback,[Organised by],"Count",COUNTAX(CALCULATETABLE(generate(SUMMARIZE(Calendar1,Calendar1[Fiscal Year]),SUMMARIZE(Feedback,[Organised by])),FILTER(all(Calendar1),Calendar1[Fiscal Year]<=VALUES(Calendar1[Fiscal Year])&&Calendar1[Fiscal Year]>=VALUES(Calendar1[Fiscal Year])-1)),[clients visited])),[count]=2))

Can you please help me identify the error.

 

[MD610]

Ashish,

I believe you are now experiencing the first error I discussed concerning VALUES(). Try wrapping your measure in a test for HASONEVALUE() like my measure is.


The format should be: =IF(HASONEVALUE(Calendar1[Fiscal Year]), [Your Measure], BLANK())

The HASONEVALUE() test will eliminate subtotals, but I'm not sure how meaningful they are anyway in your data. If you want them, the can be added back by replacing the BLANK() of the HASONEVALUE() test with a SUMX() around your measure.

 

[Ashish Mathur]

Hi,

That does not work.