I would like to find which accounts has repeated activities over a a span of a from a few years from 2012-2020. The return should be a yes or no.
You could use IF ( ( ISBLANK ( 2012 ) + ISBLANK ( 2013 ) … ) > 1, “Yes”, “No” )
But it looks like your data is not in the right format, you should unpivot it so that years are in one single column.
Thank You for posting your query onto the Forum.
Firstly, I would like to recommend you to unpivot your “Years” column and have a single column for Years. You can achieve by performing the transformations in the Query Editor.
Secondly, it’s always difficult to find out a result in absence of the PBIX file. But still you can give this formula a go and see if this works out for you. I’m hoping that MeasureName column contains unique values to achieve this type of result.
No. of Activities Repeated = IF( DISTINCTCOUNT( Table[MeasureName] ) > 1 , "YES" , "NO" )
So now since your Years data is un-pivoted, if the MeasureName occurs for more than one year then it will evaluate to true and will return the result as “YES” or else return “NO” in case of false.
I’m also providing a link below about Forum Guide. Please go through the guidelines. And also if your data contains sensitive information then you can mask your data and create a mock up file. There’s a video created on this topic by one of our Experts @BrianJ and I’m also providing a link below of it. And lastly also providing a link about how you can unpivot your Years column. For more information - you can go through the course “Advanced Data Transformations and Modelling” available on the education portal.
If this formula does not evaluate to a result that you’re looking for, then please provide the mock-up/sample file so that it helps the members of our forum to analyze your query and can come up with solution in timely manner.
Thanks & Warm Regards,
You can use PQ to create this custom column:
let _lst = List.Distinct(List.ReplaceValue(List.Skip(Record.FieldValues(_), 4) , "", null, Replacer.ReplaceValue)) in if List.Count(_lst) > 2 then 1 else 0
Also, you can unpivot and create this column using DAX:
HasRepeatedValues = IF(CALCULATE(DISTINCTCOUNT('Table'[Value]), ISBLANK('Table'[Value]) = FALSE(), ALL('Table'[Attribute])) > 1, 1, 0)
llchoong.pbix (47.3 KB)
Hi @llchoong, did the response provided by the contributors help you solve your query? If not, how far did you get, and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!
Thanks for following up. I worked on some of the above solutions and will be responding to which one worked based on the problem I have. Thank you for sharing the above tips especially how to mask sensitive information. All the support from this site has been EXTREMELY helpful!
It’s great to know that you are making progress with your query @llchoong. Please don’t forget if your question has been answered within the forum it is important to mark your thread as ‘solved’. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!