Help getting a count with filters

[Anonymous]

I think its a very simple solution but I can't get the answer 

ID	Description	Date
CS	Programmed	2/2/2023
CS	Purchase	2/2/2023
CS	Programmed	3/10/2023
CS	Programmed	5/9/2023
CS	Purchase	4/14/2023
CJ	Programmed	1/30/2023
CJ	Purchase	1/30/2023
CJ	Programmed	6/9/2023
CJ	Purchase	5/19/2023
CI	Purchase	5/19/2023
CI	Purchase	6/20/2023
CI	Programmed	4/28/2023
CI	Programmed	6/21/2023

I want to count the amount of purchase that were made in the same day of the programmed by ID and then divide by another measure

This is what I have for now but I think I'm overcomplicating 

% of compliance =

CALCULATE(

    COUNTX(

    ADDCOLUMNS(

        CALCULATETABLE(

            SUMMARIZE(Data, Data[ID], Data[Date]),

            Data[Description] = "Purchase"

        ),

        "@same",

            var _dt = Data[Date]

            return

                CALCULATETABLE(

                    VALUES(Data[Date]),

                    REMOVEFILTERS(Data[Date]),

                    Combinar2[Evento] = "Programmed"),

    [@same]

))/[Amount of weeks programmed to today])

[Anonymous]

I followed everything, just changed 

Data[Evento] = "Programmed"

to 

Data[Description] = "Programmed"

because it's in the same column but I receive this error message.

A single value for column 'Evento' in table 'Data completa' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

[Anonymous · Accepted solution]

@amitchandak 

Helped me with this solution

You have to create 2 measures.

The first one would be to create a measure filtering by Programmed

Programmed = COUNTROWS(FILTER(Data, Data[Description] = "Programmed"))

then create another measure that counts the days that are the same

On Same Date = 
COUNTROWS(FILTER(Data, not(ISBLANK([Programmed])) && not(ISBLANK(countx(filter(all(data), Data[ID] = EARLIER(Data[ID]) && Data[Date] = EARLIER(Data[Date]) && Data[Description] = "Purchase"), Data[ID])))))

[Anonymous]

Hi @Anonymous ,

Try it:

% of compliance =
VAR _ProgrammedDates =
    FILTER(
        VALUES(Data[Date]),
        Data[Evento] = "Programmed"
    )
RETURN
    DIVIDE(
        COUNTX(
            FILTER(
                ALL(Data),
                Data[Description] = "Purchase"
                    && Data[Date] IN _ProgrammedDates
            ),
            Data[ID]
        ),
        [Amount of weeks programmed to today]
    )

Best Regards,

Neeko Tang

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