Measure to count by selected criteria (first date and postal code)

[Anonymous]

Having the following table:

#	Date	UUID	PostalCode
1	01-01-19	AAA	10001
2	01-01-19	AAA	10002
3	01-02-19	AAA	10003
4	01-05-19	BBB	10001
5	01-05-19	CCC	10004

In a visual table where each row represents a "PostalCode", need a measure to provide:

- The count of "UUID" only for the first (earliest/min) date and related "PostalCode"
- In the case of two different "PostalCodes" with same "Date" and "UUID", the expected result is the MIN of the "PostalCodes"

Expected visual table result:

PostalCode	Measure	Comments
10001	2	Count of #1 & #4
10004	1	Count of #5

Thanks

[JarroVGIT]

Your example doesn't seem to match up with the comments:

- The count of "UUID" only for the first (earliest/min) date and related "PostalCode" --> I would expect in your example that for PostalCode 10001, this count would be 1 (the count of UUID=AAA with PostalCode=10001). However, in your comment you refer to row 1 and 4 (which has a UUID of BBB). 

Also, why is 10002 and 10003 not showing in your example result visual? Is this because AAA was used for 10001 at a date that is earlier than the occurence of 10002 and 10003?

Could you please clarify, then we can see how we can help you out 🙂 

[Anonymous]

Sure. Each UUID can only be counted once. For the UUID "AAA" (related to rows #1, 2 and 3) just the row #1 have to be counted as per the following reasons:

- #3 is ignored as its date (01-02-19) is later than #1 and #2 (01-01-19).
- Having #1 and #2 left, #2 is ignored as its PostalCode is higher (10002) than #1 (10001)
- Then, #1 has to be counted (PostalCode 10001)

The other UUIDs "BBB" and "CCC" only appear in one PostalCode each one, and are counted for those specific PostalCodes:

- "BBB": 10001
- "CCC": 10004

Taking the above into consideration, the expected visual table result is as follows (same as original post):

PostalCode	Measure	Comments
10001	2	Count of #1 & #4
10004	1	Count of #5

 Thank you

[v-joesh-msft · Accepted solution]

Hi @Anonymous ,

To get the result you want, you can try the following method:

1. Create a calculated column as follows:

test-PostalCode =
VAR mind =
    MINX (
        FILTER ( 'Table', 'Table'[UUID] = EARLIER ( 'Table'[UUID] ) ),
        'Table'[Date]
    )
RETURN
    CALCULATE (
        MIN ( 'Table'[PostalCode] ),
        FILTER ( ALLEXCEPT ( 'Table', 'Table'[UUID] ), 'Table'[Date] = mind )
    )

2. Create a measure as follows:

Measure = DISTINCTCOUNT('Table'[UUID])

Results are as follows:

Here is a demo, please try it:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EVs6JVESPV9Ptkt7jXf4fDwB5E6hfCcB6FatmpL85tUuVA?e=jbK3a5 

Best Regards,

Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you very much Joey!

The provided solution indeed solves the proposed question, though the iniital request was to do it using a measure and not through a calculated column.

The question now would be: is it posible to do it just with a measure, or it is 100% need to use a calculated column?

Best regards

[v-joesh-msft]

Hi @Anonymous ,

I think you can't achieve the result you want with only one measure. Since each row represents a "PostalCode", a calculated column is necessary.

Best Regards,

Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.