Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

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 ...
Need Help
  • v-joesh-msft's avatar
    v-joesh-msft
    6 years ago

    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's avatar
Anonymous
Not applicable
6 years ago

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):

 

PostalCodeMeasureComments
100012Count of #1 & #4
100041Count of #5

 

 Thank you

v-joesh-msft's avatar
v-joesh-msft
Icon for Solution Sage rankSolution Sage
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      v-joesh-msft
      Icon for Solution Sage rankSolution Sage
      6 years ago

      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.