Forum Discussion

TomSinAA's avatar
TomSinAA
Icon for Helper IV rankHelper IV
1 year ago
Solved

Measure to group and count rows

I have data that is structured like this: 

M-IDS-IDEnteredBy
M-1S1C
M-1S2C
M-1S3C
M-1S4C
M-2S1C
M-2S2D
M-2S3NE
M-3S1C
M-3S2D
M-3S3D
M-3S4D
M-3S5D
M-3S6

D

 

I have a measure that provides a grouping by enteredby value: Entered By Group = If(ISBLANK(Table1[Entered]),"Not Entered", If(Find("D",TMSShipmentDetail[EnteredBy],1,99)<>99,"D-group,"C-group"))

 

I would like to add another measure to count at the M-ID level. So I can get a count showing all S-IDs entered by C or not all S-IDs entered by C.  So for the example data above the table would look like this: 

Count of M-IDsGrouping
1All S-ID entered by C-group
2Not All S-IDs entered by C Group
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi TomSinAA ,

     

    First create an enter table:

    Then please create this measure:

    Count of M-IDs = 
VAR __table = SUMMARIZE('Table1','Table1'[M-ID],'Table1'[S-ID],"@EnteredBy",[Entered By Group])
VAR __c_sid = CALCULATETABLE(VALUES(Table1[S-ID]),FILTER(__table,[@EnteredBy]="C"))
VAR __not_c_sid = CALCULATETABLE(VALUES(Table1[S-ID]),FILTER(__table,[@EnteredBy]<>"C"))
VAR __only_c_count = COUNTROWS(EXCEPT(__c_sid,__not_c_sid))
VAR __not_only_c_count = COUNTROWS(EXCEPT(__not_c_sid,__c_sid))
VAR __result = IF(SELECTEDVALUE('Table'[Grouping])="All S-ID entered by C-group",__only_c_count,__not_only_c_count)
RETURN
__result

    Output:

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum 

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi TomSinAA ,

     

    First create an enter table:

    Then please create this measure:

    Count of M-IDs = 
VAR __table = SUMMARIZE('Table1','Table1'[M-ID],'Table1'[S-ID],"@EnteredBy",[Entered By Group])
VAR __c_sid = CALCULATETABLE(VALUES(Table1[S-ID]),FILTER(__table,[@EnteredBy]="C"))
VAR __not_c_sid = CALCULATETABLE(VALUES(Table1[S-ID]),FILTER(__table,[@EnteredBy]<>"C"))
VAR __only_c_count = COUNTROWS(EXCEPT(__c_sid,__not_c_sid))
VAR __not_only_c_count = COUNTROWS(EXCEPT(__not_c_sid,__c_sid))
VAR __result = IF(SELECTEDVALUE('Table'[Grouping])="All S-ID entered by C-group",__only_c_count,__not_only_c_count)
RETURN
__result

    Output:

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum