problem with counting rows where a slicer has blanks

[flying_bird]

Hell all, i am new to power BI and got stuck with a problem. I am trying to update daily response rate in a dashboard. 

I have created three new measures: 

total invitation = countrows(Sheet1)

Cummulative responses (all responses up until the latest)

and then response rate as : 

divide([Cumulative Responses], [total invitation],0)

Then the numbers shown on dashboard are not correct:

I don't want total invitation to change based on the dates specified as they are not related. somehow Power BI thought this is what i need. Can anyone please advise?  The total invitation here should be 10 if i don't specify any other slicers...

Thanks a lot!

[danextian]

Hi @flying_bird 

For the total response rate

CALCULATE ( COUNTROWS ( Sheet1 ), REMOVEFILTERS ( Sheet1 ) )

[flying_bird]

Apologies. Seem like there are still issues. I think the problem is with how cummulative responses are defined.

i would love to have it defined as all the responses up to a single date i picked in the slicer. 

I expect 60% response rate for A and 40% rate for B when I select only 2/4/25 on the bar chart using the data i shared in my orginal post.

The card says one rate while the chart says another.

Can anyone please help?

Thank you.

[Anonymous]

Hi @flying_bird 

Please check if this is what you want. 

Total Cummulative Responses = 
CALCULATE(
    COUNTROWS(Sheet1),
    FILTER(
        ALL(Sheet1),
        Sheet1[Response Date] <= MAX(Sheet1[Response Date]) &&
        NOT(ISBLANK(Sheet1[Response Date]))
    )
)

Cummulative Responses = 
CALCULATE(
    COUNTROWS(Sheet1),
    ALLEXCEPT(Sheet1,Sheet1[Slicer 1]),
    Sheet1[Response Date] <= MAX(Sheet1[Response Date]),
    NOT(ISBLANK(Sheet1[Response Date]))
)

Response Rate = DIVIDE([Cummulative Responses], [Total Cummulative Responses], 0) 

Best regards, 

Jing

[Irwan]

hello @flying_bird 

please check if this accomodate your need.

1. create a new measure with following DAX for Total Invitation

Total Invitation = 
CALCULATE(
    COUNTROWS('Table'),
    ALL('Table')
)

2. create a new measure with following DAX for Cumulative Response (exact same DAX as yours)

Cumulative Response = 
CALCULATE(
    COUNTROWS('Table'),
    FILTER(
        'Table',
        'Table'[Response Date]<=MAX('Table'[Response Date])&&
        not ISBLANK('Table'[Response Date])
    )
)

3. create a new measure with following DAX for Response Rate (exact same DAX as yours)

Response Rate =
DIVIDE(
    [Cumulative Response],
    [Total Invitation]
)

Hope this will help.

Thank you.

[flying_bird]

This seems not working... but thank you for trying.