How to sum daily distinct count over the month ?

[Anonymous]

Hi,

I'm new to the Power Bi community, and I can't find any solution to my probleme 😞

So I'm trying here to get some help, hoping a Power Bi veteran can help me !

So here is my issue (see the screenshot)

I have data about my email campaigns, and I want for every email campaigns to show the count of delivered, opened and clicked message by distinct email over the month.

The problem is that the distinct count on the emails is done over the month, whereas I would like it to be done per day and after being able to sum the daily distinct values over the month.

Is there a way to achieve this ?

Thank you very much for your time,

[v-lionel-msft]

Hi @Anonymous ,

Measure = 
VAR x = 
CALCULATE(
    SUM([Values]),
    FILTER(
        ALL(Sheet7),
        Sheet7[Category] = MAX(Sheet7[Category]) && Sheet7[Item] = MAX(Sheet7[Item]) && Sheet7[Month] = MAX(Sheet7[Month])
    )
)
RETURN
IF(
    HASONEFILTER( Sheet7[Item]),
    x,
    SUMX(
        FILTER( Sheet7, Sheet7[Category] = MAX(Sheet7[Category]) ),
        [Values]
    )
)

If not please show the sample data of the fact tables and expected output results .

Best regards,
Lionel Chen

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

[Anonymous]

Thanks you both for your answer ! 

The first one worked well for me so thanks again 🙂

Stanislas

[amitchandak · Accepted solution]

@Anonymous , Try something like

sumx(Values(Table[date]), calculate(distinctcount(Table[email])))