topic Re: Sum of Count(column) > 2 in a measure in DAX Commands and Tips

topic Re: Sum of Count(column) > 2 in a measure in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Sum-of-Count-column-gt-2-in-a-measure/m-p/2893769#M94004 <P>Hi,</P> <P>Write these measures</P> <P>Count = countrows(Data)</P> <P>Measure1 = SUMX(filter(values(Data[Person]),[count]>=2),[count])</P> <P>Drag Measure1 to a card visual.</P> <P>Hope this helps.</P> Tue, 08 Nov 2022 23:43:58 GMT Ashish_Mathur 2022-11-08T23:43:58Z Sum of Count(column) > 2 in a measure https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Sum-of-Count-column-gt-2-in-a-measure/m-p/2892751#M93938 <P>Hello to all,</P><P><img /></P><P> </P><P>As you see in the above snapshot - </P><P>Card show the overall count of Person (Names are repeated), I'm trying to achieve the total count of person with person repeating count is greater than or equal to 2. Based on filter it easy to get the value in the table, I need help in getting this value in a measure.</P><P> </P><P>The data is sensitive hence I have purposely hidden the names in the table.</P><P> </P><P> </P><P><a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="313" data-lia-user-login="Greg_Deckler" class="lia-mention lia-mention-user">Greg_Deckler</a> <BR /><a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="148838" data-lia-user-login="amitchandak" class="lia-mention lia-mention-user">amitchandak</a> </P><P><a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="450074" data-lia-user-login="Shaurya" class="lia-mention lia-mention-user">Shaurya</a> </P><P><a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="19851" data-lia-user-login="Ashish_Mathur" class="lia-mention lia-mention-user">Ashish_Mathur</a> </P><P> </P><P> </P> Tue, 08 Nov 2022 15:29:49 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Sum-of-Count-column-gt-2-in-a-measure/m-p/2892751#M93938 aakif_aslam 2022-11-08T15:29:49Z Re: Sum of Count(column) > 2 in a measure https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Sum-of-Count-column-gt-2-in-a-measure/m-p/2893460#M93994 <P class="lia-align-justify">Hi <a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="275642" data-lia-user-login="aakif_aslam" class="lia-mention lia-mention-user">aakif_aslam</a>,</P><P class="lia-align-justify"> </P><P class="lia-align-justify">You can create a count column in DAX using:</P><P class="lia-align-justify"> </P><LI-CODE lang="java">Count = SUMX('Table',IF('Table'[Person]=EARLIER('Table'[Person]),1,0))</LI-CODE><P class="lia-align-justify"> </P><P class="lia-align-justify">And then use this formula for your measure:</P><P class="lia-align-justify"> </P><LI-CODE lang="java">Number of People = CALCULATE(DISTINCTCOUNT('Table'[Person]),FILTER('Table','Table'[Count]>=2))</LI-CODE><P class="lia-align-justify"> </P><P class="lia-align-justify"><EM><STRONG><I>Works for you? Mark this post as a solution if it does!<BR />Consider taking a look at my blog: <LI-MESSAGE title="How to Export Telemetry Data from Azure IoT Central into Power BI" uid="2845191" url="https://community.powerbi.com/t5/Community-Blog/How-to-Export-Telemetry-Data-from-Azure-IoT-Central-into-Power/m-p/2845191#U2845191" discussion_style_icon_css="lia-mention-container-editor-message lia-img-icon-blog-thread lia-fa-icon lia-fa-blog lia-fa-thread lia-fa"></LI-MESSAGE></I></STRONG></EM></P> Tue, 08 Nov 2022 20:33:45 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Sum-of-Count-column-gt-2-in-a-measure/m-p/2893460#M93994 Shaurya 2022-11-08T20:33:45Z Re: Sum of Count(column) > 2 in a measure https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Sum-of-Count-column-gt-2-in-a-measure/m-p/2893769#M94004 <P>Hi,</P> <P>Write these measures</P> <P>Count = countrows(Data)</P> <P>Measure1 = SUMX(filter(values(Data[Person]),[count]>=2),[count])</P> <P>Drag Measure1 to a card visual.</P> <P>Hope this helps.</P> Tue, 08 Nov 2022 23:43:58 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Sum-of-Count-column-gt-2-in-a-measure/m-p/2893769#M94004 Ashish_Mathur 2022-11-08T23:43:58Z Re: Sum of Count(column) > 2 in a measure https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Sum-of-Count-column-gt-2-in-a-measure/m-p/2893974#M94012 <P>Hi, <a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="275642" data-lia-user-login="aakif_aslam" class="lia-mention lia-mention-user">aakif_aslam</a> </P> <P>Here are the steps you can refer to :</P> <P>(1)This is my test data:</P> <P><img /></P> <P>(2)We can create a measure like this:</P> <LI-CODE lang="markup">Count >2 = var _t =FILTER( SUMMARIZE('Table','Table'[Person],"Count",COUNT('Table'[Person])) , [Count]>2) return COUNTROWS(_t)</LI-CODE> <P>(3)Then we can meet your need ,the result is as follows:</P> <P><img /></P> <P> </P> <P>Best Regards,</P> <P>Aniya Zhang</P> <P>If this post <STRONG>helps</STRONG>, then please consider <STRONG><EM>Accept</EM></STRONG><EM> it as the solution</EM> to help the other members find it more quickly</P> Wed, 09 Nov 2022 01:50:42 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Sum-of-Count-column-gt-2-in-a-measure/m-p/2893974#M94012 v-yueyunzh-msft 2022-11-09T01:50:42Z