Countrows not working as expected

[Anonymous]

Hello,

Within DAX I am trying to calculate the amount of days a Client is connected to one of the Dimensions. So for example how many times a Client is connected to a certain team within a certain timeframe.

This must give the result of 3. Within this time period the Client 2. Is connected 3 times to Team 3.

As you can see in the example below, my solution does not work (check below for measure). The yellow marked totals needs to be the sum of all underlying Clients. Now it only gives the max amount of days within that month. Can someone please explain what is going wrong?

Measure I am now using: 

CALCULATE(COUNTROWS(Dim_Date), FILTER(Fact_Client, Fact_Client[Client_ID] <> blank() ))

Thanks!

[v-kkf-msft]

Hi @Anonymous ,

Create the new measure:

Total = 
IF(
    ISFILTERED(Fact_Client[Team_ID]),
    [Measure],
    SUMX( ALLSELECTED(Fact_Client[Team_ID]), [Measure] )
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[Anonymous]

Thanks Winniz for your answer.

But unfortunately this does not completely solves the problem.

test_measure = CALCULATE(COUNTROWS(Dim_Date), FILTER(Fact_Client, Fact_Client[Client_ID] <> blank() ))

Total = IF(ISFILTERED(Fact_Client[Team_ID]),[test_measure],SUMX( ALLSELECTED(Fact_Client[Team_ID]), [test_Measure] ))

As you can see in the example above. The totals does not show what I want. For example in the left table the totals are still 31 for the Team_A totals. In the right table the totals for example Client_A for City_A is 73, while there only 31 days within the month 2019-12.

Above you can see the data model, it also contains more then 1 dimension. So the measure also need to be dynamic while switching with dimensions in your table.

Thanks!

[v-kkf-msft · Accepted solution]

Hi @Anonymous ，

What kind of data do you want to display in the subtotal section?

If you want to calculate how many days you have contact with different teams in this month, then I think your measure is correct.

Take a look at the following example data. In December, there are 29 days including Team_ID = 1, and the return value of the measure is also 29.

Best Regards,
Winniz