topic Re: Check if customer reference contains full data. in DAX Commands and Tips

Check if customer reference contains full data.

Topjacket — Tue, 04 Oct 2022 15:36:10 GMT

I am hoping this will be easy but I can't get it to work. I am trying to add a column (so I can filter certain records from output) which counts the distinct number of "Month & Year" in the entire data set and compares it to the distinct number of "Month & Year" for the "Customer ID". I would then like to return a simple TRUE or FALSE if the "Customer ID" in that row has a full data set (i.e. Distinct Count "Month & Year" for ALL = Distinct Count "Month & Year" for Customer ID)

Would be great to know how to do this or if there is a slicker way to ahieve the same.

Thanks

Re: Check if customer reference contains full data.

amitchandak — Tue, 04 Oct 2022 17:48:13 GMT

Topjacket, a new column

if(distinctcount(Table[Month Year]), calculate(distinctcount(Table[Month Year]),filter(Table, Table[Customer]= earlier(Table[Customer]))) , "Yes", "No")

Re: Check if customer reference contains full data.

v-yueyunzh-msft — Wed, 05 Oct 2022 08:07:04 GMT

Hi , Topjacket

Here are the steps you can refer to :

(1)This is my test data :

(2)We can create a caluclated column :

Flag = var _total_count = COUNTROWS( ALL('Table6'[Month & Year])) var _current_ID = 'Table6'[Customer ID] var _ID_count = COUNTROWS(DISTINCT( SELECTCOLUMNS( FILTER( 'Table6' , 'Table6'[Customer ID] = _current_ID ) ,"month_year" , [Month & Year] ))) return IF(_total_count = _ID_count , TRUE() , FALSE())

(3)Then we can meet your need , the result is as follows:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Re: Check if customer reference contains full data.

Topjacket — Thu, 06 Oct 2022 12:25:02 GMT

Hi Both

Thanks very much for the replies.

amitchandak I did try this dax but was getting an error message saying there were too many paramaters. Any ideas?

v-yueyunzh-msft I tried this formula and it did not error but also it set all records to "Yes" which is not correct.

I have placed an example table below with the desired output (Full Data Set?). Customer ABC is returning "No" because the entire data set has 4 months of data but customer ABC only has 3 months.

Any advice you can offer would be great.

Thanks again

CustomerID	MonthAndYear	FullDataSet?
1	Jan 2020	Yes
1	Feb 2020	Yes
1	Mar 2020	Yes
1	Apr 2020	Yes
ABC	Jan 2020	No
ABC	Feb 2020	No
ABC	Mar 2020	No
ABC123	Jan 2020	Yes
ABC123	Feb 2020	Yes
ABC123	Mar 2020	Yes
ABC123	Apr 2020	Yes

Re: Check if customer reference contains full data.

amitchandak — Thu, 06 Oct 2022 12:27:00 GMT

Topjacket , Sorry my bad, used , inplace of =

if(distinctcount(Table[Month Year])= calculate(distinctcount(Table[Month Year]),filter(Table, Table[Customer]= earlier(Table[Customer]))) , "Yes", "No")

Re: Check if customer reference contains full data.

Topjacket — Thu, 06 Oct 2022 13:27:29 GMT

Thanks very much amitchandak

I thought I had tried different combos but I obviously missed this one. All working great now.

Thanks again.