Need a new column with count of occurrences (Direct Query)

[TeaBags]

Hi all - newbie here.   I am trying to add a calculated column that shows a count of occurrences of "Member" in my dataset.  I have a single data table, direct query from SQL.   I've been researching options here and Reddit but I can't use things like COUNT, COUNTDISTINCT in a column because of direct query.   I've also tried some formulas here with EARLIER such as .. 

Occurrence = COUNTROWS(FILTER(IData[Member]=EARLIER(IData[Member]))) but get "too few arguments" error.  

(https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Calculated-Column-To-Count-Occurrenc...)

I can get the data in a table viz as shown below, but I need it as a column  so I can visualize n number of members called x  times (Aggregated) in a bar chart or line chart.  I also need to slice on that data (i.e. select "6" to show filtered members who called 6 times).  Without the data in a column, I don't seem to be able to do either of these.  

Thanks in advance!  

[v-heq-msft · Accepted solution]

Hi @TeaBags ,
Based on your description, you can't use count or countdistinct in data fetched in Direct Query mode. you might want to consider creating a view on the sql side first to aggregate this data. For example, you can use

CREATE VIEW MemberCount AS
SELECT Member, COUNT(*) AS Occurrences
FROM Table
GROUP BY Member;

Then when you get to the database view in Direct Query mode, you can use this dax expression

Member Occurrences = COUNTROWS(FILTER(Table, Table[Member] = "Member"))

That might accomplish your goal.

Best regards,

Albert He

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

[TeaBags]

Thanks Albert!  That did the trick for my immediate need as I have full access to the SQL instance being used.