topic Count occurrences of user in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Count-occurrences-of-user/m-p/1936362#M42124 <P>hi i want to create two mesures,&nbsp;</P><P>one that calculate new user and one that&nbsp;calculate&nbsp; return user</P><TABLE border="1"><TBODY><TR><TD>date</TD><TD>userid</TD><TD>activityid</TD></TR><TR><TD>1/1/2020</TD><TD><STRONG>1</STRONG></TD><TD><STRONG>1</STRONG></TD></TR><TR><TD>1/1/2020</TD><TD><STRONG>2</STRONG></TD><TD><STRONG>2</STRONG></TD></TR><TR><TD>1/1/2020</TD><TD><STRONG>1</STRONG></TD><TD><STRONG>3</STRONG></TD></TR><TR><TD>2/1/200</TD><TD><STRONG>1</STRONG></TD><TD><STRONG>4</STRONG></TD></TR><TR><TD>2/1/2020</TD><TD><STRONG>1</STRONG></TD><TD><STRONG>5</STRONG></TD></TR><TR><TD>3/1/2020</TD><TD><STRONG>1</STRONG></TD><TD><STRONG>6</STRONG></TD></TR></TBODY></TABLE><P>&nbsp;</P><P>user number 1 will be return user , becuse he will apper in the recoreds in a few diffrent date.</P><P>user 2 will be a new user, becuse he apper only on the first day of the records&nbsp;</P><P>&nbsp;</P><P>&nbsp;</P><P>&nbsp;</P><P>&nbsp;</P><P>&nbsp;</P><P>&nbsp;</P> Sun, 04 Jul 2021 06:16:00 GMT rco123456 2021-07-04T06:16:00Z Count occurrences of user https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Count-occurrences-of-user/m-p/1936362#M42124 <P>hi i want to create two mesures,&nbsp;</P><P>one that calculate new user and one that&nbsp;calculate&nbsp; return user</P><TABLE border="1"><TBODY><TR><TD>date</TD><TD>userid</TD><TD>activityid</TD></TR><TR><TD>1/1/2020</TD><TD><STRONG>1</STRONG></TD><TD><STRONG>1</STRONG></TD></TR><TR><TD>1/1/2020</TD><TD><STRONG>2</STRONG></TD><TD><STRONG>2</STRONG></TD></TR><TR><TD>1/1/2020</TD><TD><STRONG>1</STRONG></TD><TD><STRONG>3</STRONG></TD></TR><TR><TD>2/1/200</TD><TD><STRONG>1</STRONG></TD><TD><STRONG>4</STRONG></TD></TR><TR><TD>2/1/2020</TD><TD><STRONG>1</STRONG></TD><TD><STRONG>5</STRONG></TD></TR><TR><TD>3/1/2020</TD><TD><STRONG>1</STRONG></TD><TD><STRONG>6</STRONG></TD></TR></TBODY></TABLE><P>&nbsp;</P><P>user number 1 will be return user , becuse he will apper in the recoreds in a few diffrent date.</P><P>user 2 will be a new user, becuse he apper only on the first day of the records&nbsp;</P><P>&nbsp;</P><P>&nbsp;</P><P>&nbsp;</P><P>&nbsp;</P><P>&nbsp;</P><P>&nbsp;</P> Sun, 04 Jul 2021 06:16:00 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Count-occurrences-of-user/m-p/1936362#M42124 rco123456 2021-07-04T06:16:00Z Re: Count occurrences of user https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Count-occurrences-of-user/m-p/1936379#M42125 <P><img /></P><P>&nbsp;</P><DIV><DIV><STRONG><EM>New User Measure : = </EM></STRONG></DIV><DIV><EM>VAR _firstdayoftherecords =</EM></DIV><DIV><EM>CALCULATE ( MIN ( Data[date] ), REMOVEFILTERS ( Data ) )</EM></DIV><DIV><EM>VAR _newtable =</EM></DIV><DIV><EM>ADDCOLUMNS (</EM></DIV><DIV><EM>SUMMARIZE ( Data, Users[userid] ),</EM></DIV><DIV><EM>"@firstdaycount", CALCULATE ( COUNTROWS ( Data ), Dates[date] = _firstdayoftherecords ),</EM></DIV><DIV><EM>"@otherthanfirstdaycount", CALCULATE ( COUNTROWS ( Data ), Dates[date] &lt;&gt; _firstdayoftherecords )</EM></DIV><DIV><EM>)</EM></DIV><DIV><EM>VAR _filtertableforcount =</EM></DIV><DIV><EM>FILTER ( _newtable, [@firstdaycount] &gt; 0 &amp;&amp; [@otherthanfirstdaycount] = 0 )</EM></DIV><DIV><EM>RETURN</EM></DIV><DIV><EM>COUNTROWS ( _filtertableforcount )</EM></DIV><DIV><EM>&nbsp;</EM></DIV><DIV><EM>&nbsp;</EM></DIV><DIV><DIV><DIV><EM><STRONG>Return User Measure : =</STRONG> </EM></DIV><DIV><EM>VAR _firstdayoftherecords =</EM></DIV><DIV><EM>CALCULATE ( MIN ( Data[date] ), REMOVEFILTERS ( Data ) )</EM></DIV><DIV><EM>VAR _newtable =</EM></DIV><DIV><EM>ADDCOLUMNS (</EM></DIV><DIV><EM>SUMMARIZE ( Data, Users[userid] ),</EM></DIV><DIV><EM>"@firstdaycount", CALCULATE ( COUNTROWS ( Data ), Dates[date] = _firstdayoftherecords ),</EM></DIV><DIV><EM>"@otherthanfirstdaycount", CALCULATE ( COUNTROWS ( Data ), Dates[date] &lt;&gt; _firstdayoftherecords )</EM></DIV><DIV><EM>)</EM></DIV><DIV><EM>VAR _filtertableforcount =</EM></DIV><DIV><EM>FILTER ( _newtable, [@firstdaycount] &gt; 0 &amp;&amp; [@otherthanfirstdaycount] &gt; 0 )</EM></DIV><DIV><EM>RETURN</EM></DIV><DIV><EM>COUNTROWS ( _filtertableforcount )</EM></DIV><DIV>&nbsp;</DIV><DIV>&nbsp;</DIV><DIV><SPAN><A href="https://www.dropbox.com/s/ml9isvplv84zk8x/74.pbix?dl=0" target="_self">https://www.dropbox.com/s/ml9isvplv84zk8x/74.pbix?dl=0</A>&nbsp;</SPAN></DIV><DIV>&nbsp;</DIV><DIV>&nbsp;</DIV></DIV></DIV></DIV> Sun, 04 Jul 2021 07:30:00 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Count-occurrences-of-user/m-p/1936379#M42125 Jihwan_Kim 2021-07-04T07:30:00Z