Forum Discussion
Advocate IIFilters created using calculated table does not give the desired result - Need Help
I think you are telling us that you filtered for 2'visits' from the slicer but the table visual shows 1 'visit' for some records.
The other slicer has affected the result. Let's look at the first row. For the 5286 mobile there are 2 distinct 'bill no' in the customer data (ending 6309 and 2566) but there are 3 records for 6309. Individually , each row has bill value < 1000 but when you constructed the dimension table 'unique bill number', you added the value of each record, so it added up to more than 1000. That value was used in the 2nd slicer.
So your table visual is 'show me mobile numbers with 2 visits and, of those visits, show me mobile numbers which have total bill value of less than 1000'. Hence only 1 'visit' qualifies.
You are liberally mixing columns from the fact table and the dimension tables. Guidance is to use columns from the dimension tables for filtering, and columns from the fact table for counting.
I also adjusted your SWITCH statements. They were the wrong way round.
Advocate IIThe expected outcome should match as follows;
- There are 186 invoices with bill value below $1,000 from 1st Sep 2024 to 30th Sep 2024.
- Only a few out of these 186 invoices are generated from customers (unique mobile no.) who visited thrice within this date range as shown in the expected outcome. Rest have either visited twice or once only & such customers should not be excluded when we filter for 3 visits.
Here is the full data along with the current (grey) vs expected outcome (green).
Super User
This is a calculated column that is not impacted by the filters in your data model.
You need to use a measure instead.