Two rules in one function and table

[Anonymous]

Hello, 

I would like to create a function which distinct counts or avoids repeats of the same name, it only counts the date issued for each P01, P02 or P03 but not Proposed Date Issued and it only does the latest date. Thanks. 

My example attempt below which does not work: 

ALL  Issue Date - Actuals = CALCULATE(COUNTROWS(CARTABLE),DISTINCTCOUNTNOBLANK(CARTABLE[Name]),FILTER(CARTABLE,MAX(CARTABLE[Date])),FILTER(CARTABLE,CARTABLEAttribute]IN {"P01 Date Issued","P03 Date Issued","P02 Date Issued","Date Issued"}))

Name	Revision	Attribute	Date
Red Car	P01	P01 Date Issued	11/11/2022
Blue Car	P01	P01 Date Issued	11/11/2022
Red Car	P01	P01 Date Issued	11/11/2022
Red Car	P01	P02 Date Issued	13/11/2022
Red Car	P01	P02 Proposed Date Issued	12/11/2022
Blue Car	P01	P03 Date Issued	14/11/2022
Blue Car	P01	P02 Date Issued	13/11/2022
Green Car	P01	Date Issued	11/11/2022
Green Car	P01	Date Issued	11/11/2022
Blue Car	P01	P01 Proposed Date Issued	11/11/2022

[FreemanZ]

hi @Anonymous 

Not so sure about your expectation. Are you expecting 6 from the measure?

If so, try to create a measure like this:

ALL  Issue Date - Actuals = 
VAR _table1 = 
ADDCOLUMNS(
    SUMMARIZE(
        CARTABLE,
       CARTABLE[Name],
       CARTABLE[Revision],
       CARTABLE[Attribute]
    ),
    "MaxDate",
    CALCULATE(MAX(CARTABLE[Date]))
)

VAR _table2 = 
FILTER(
    _table1,
   CARTABLE[Attribute] IN {"P01 Date Issued","P03 Date Issued","P02 Date Issued","Date Issued"}
)

RETURN COUNTROWS(_table2)

it worked like this:

[Anonymous]

Thank you for the reply, 

Sorry ignore revision column. The answer should be three. Trying to achieve a formula that takes the latest revision if it is filled in so P03 Date issued would be the latest for blue car. As there is no P03 Date issued for Red Car it counts the P02 as the latests, but if there is a P01 for the Red Car it ignores and does not count P01 Red Car and only counts the P02 from the attribute column. So, if the green car has a P01, P02, P03 it only counts the P03.

I hope this makes it more clear what trying to achieve. Thanks.

Red Car	P02 Date Issued	13/11/2022
Blue Car	P03 Date Issued	14/11/2022
Green Car	Date Issued	11/11/2022

[FreemanZ]

hi @Anonymous 

that is necessary to know. Then try:

ALL  Issue Date - Actuals = 
VAR _table1 = 
CALCULATETABLE(
    CARTABLE,
    CARTABLE[Attribute] IN {"P01 Date Issued","P03 Date Issued","P02 Date Issued","Date Issued"}
)
VAR _table2 = 
ADDCOLUMNS(
    SUMMARIZE(
       _table1,
       CARTABLE[Name],
       CARTABLE[Revision]
    ),
    "MaxDate",
    CALCULATE(MAX(CARTABLE[Date]))
)
RETURN COUNTROWS(_table2)