Forum Discussion

Topjacket's avatar
Topjacket
Helper I
3 years ago
Solved

Check if customer reference contains full data.

Hi

 

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

  • amitchandak's avatar
    amitchandak
    3 years ago

    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")

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    Topjacket, a new column

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

  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    Community Support
    3 years ago

    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

     

  • Topjacket's avatar
    Topjacket
    Helper I
    3 years ago

    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

     

    CustomerIDMonthAndYear

    FullDataSet?

    1

    Jan 2020

    		Yes
    1Feb 2020Yes
    1Mar 2020Yes
    1Apr 2020Yes
    ABC Jan 2020No
    ABCFeb 2020No
    ABCMar 2020No
    ABC123 Jan 2020Yes
    ABC123Feb 2020Yes
    ABC123Mar 2020Yes
    ABC123Apr 2020Yes

     

    • amitchandak's avatar
      amitchandak
      Super User
      3 years ago

      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")

      • Topjacket's avatar
        Topjacket
        Helper I
        3 years ago

        Thanks very much amitchandak 

         

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

         

        Thanks again.