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 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"}))

 

NameRevisionAttributeDate
Red Car P01P01 Date Issued11/11/2022
Blue CarP01P01 Date Issued11/11/2022
Red Car P01P01 Date Issued11/11/2022
Red Car P01P02 Date Issued13/11/2022
Red Car P01P02 Proposed Date Issued12/11/2022
Blue CarP01P03 Date Issued14/11/2022
Blue CarP01P02 Date Issued13/11/2022
Green CarP01Date Issued11/11/2022
Green CarP01Date Issued11/11/2022
Blue CarP01P01 Proposed Date Issued11/11/2022

3 Replies

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

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