Forum Discussion

arthurnotaro's avatar
arthurnotaro
Frequent Visitor
8 years ago

Problems with Distinct Count

I have a table with the following information   Client code, Product line, Product code, Month, Sales amount   My goal is to find out the number of clients (distinctcount) that have purchased som...
Need Help
v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
8 years ago

Hi CahabaData,

 

Do you have the similar scenario? The "This Month" has a context "current month" in the visual. So no date range is needed. Besides, the fields of the visual are important. They should be from the proper table. Try it again please.

";" is a separator for some countries. 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

BTW, if you have any special questions, please open a new thread.

 

Best Regards!

Dale

CahabaData's avatar
CahabaData
Memorable Member
8 years ago

thank you for educating me on separator variation.

 

I happen to have/need an almost identical requirement - and so copied in the suggested code, along with the exact same sample data that was posted.

 

It did not work for me, was all 0s.  I traced this back to the Calendar table.  If instead of using the Calendar table/field, I instead use the posted Table and Month field - then it works.  Not sure why the Calendar table reference returns 0 except perhaps that the Calendar table is daily while the posted table is month and there is some join issue I don't understand.

 

Also in regard to a 2nd isue - I further experiment by simplifying the final IF statement to just bare bones:

 

 IF (ThisMonth <> 0 && LastMonth <> 0 && LastTwoMonth <> 0,1,0)

 

This also worked fine (not related to the Calendar date issue).  It is not clear to me what is the benefit of CALCULATE and would welcome advice on this point.

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi CahabaData,

     

    Did you mean the date table "Calendar"? The time intelligence functions need a date table. 

     

    Best Regards!

    Dale