Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Two rules in one function and table

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

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 Issued13/11/2022
Blue CarP03 Date Issued14/11/2022
Green CarDate Issued11/11/2022
FreemanZ's avatar
FreemanZ
Super User
3 years ago

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)