Querying a table to create a value in a column in a new table

[actzikas]

I would like to create a column that will display if a reps was considered active for a given week. 

A rep in this case will be considered active if they have created 20 calls or emails (Column: "ActivityType") in a given week.

I have a column in my table that displays the "Start of the Week"

I would there for like to create some custom column on that table that flags the rep as active or inactive.

Custom Column [what I am thinking]

IF Owner has Count(ActivityType) > 20 within "Start of the Week", then Active, else Inactive 

[v-gizhi-msft · Accepted solution]

Hi,

According to your description, i create a simple sample to test:

Then create a measure to show active/inactive status:

Measure = IF(CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[StartOfWeek]))>20,"Active","Inactive")

Choose [StartOfWeek] and this measure as a table visual, it shows:

Hope this helps.

Best Regards,

Giotto Zhi

[amitchandak]

Try as a new column

if(sumx(filter(table,table[owner]=earlier(table[owner])),Count(table[ActivityType]))>20,"Active","Inactive")

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin

[Anonymous]

I would first question why this needs to be a column.  You could achieve this much more efficently as a measure.

DAX allows you to use time intelligence so you could simply have a measure work from any date and the measure could tell you (true or false) whether someone has worked within a given timeframe.

[actzikas]

could you perhaps share how you would write it as a measure? Thank you! 

[amitchandak]

@actzikas 

Can you share sample data and sample output.