Filtering Problem

[Anonymous]

Hi,

There are 3 tables

Customer(custid,...)

Customer-route(routeid,custid)

Journeydate(date,routeid)

Journeydate and Customer-route tables linked using routeid

Customer-route and customer linked with custid

one customer can be in many route

one route can have many dates in the Journeydate

requirement is to : count number of customers in a date. So if 1 month is selected then it should actually count the number of customer in the route and sum this for 1 month(thats because in 1 month, same route will be repeated)

For EG: MondayRoute

in 1 month MondayRoute will be 4/5 . so total customer count should be(count of customer for MondayRoute multiply by 4/5)

To acheive this we have created a measure in customer- route table which counts the number of customers against each route. then we created a calculated column in journey date table to bring the above measure against each row. then we created a measure which will sum this calculated column. we are getting required result but the problem is.. it is not filtering based on any attribute of customer(eg. customer status active and inactive). So when displaying the final measure against Customer Status, it is not displaying correct number although the total is correct

Thank You.

[v-chuncz-msft]

@Anonymous 

Values in a calculated column are fixed. You may use SUMX to write a measure.

[Anonymous]

Hi @v-chuncz-msft 

we are writing measure as below. still it is not filtering on any attribute of customer(eg. customer status active and inactive)

CALCULATE(countx(Customer-route,Customer-route[dw_cust_id]),GROUPBY(Customer-route,Customer-route[dw_route_id]))

could you please modify the above dax.

Thanks.