Forum Discussion
Advocate IIFilters created using calculated table does not give the desired result - Need Help
Hi,
The values of visits don't match when I use the filters I created using calculated tables. Here is a scenario & attached is the sample .pbix file Analysis.pbix
I have created 2 tables & linked them to the master data for filter purposes.
Table 1 = Customer Mobile Number shows customers' unique mobile numbers, and their unique visits i.e. distinct count of invoice numbers/bill numbers along with a DAX-based grouping defining if the customer has visited once, twice, thrice & so on.
Table 2 = Unique Bill Number which shows list of all unique invoice numbers/bill number, corresponding total bill value & a grouping to show the bucket the invlice falls in i.e. below 1000, 1000-2000 & so on.
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.
7 Replies
- HotChilli
Community Champion
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.
AmeenVanakarHi HotChilli
Thanks. This makes sense.
- lbendlin
Super User
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.
- AmeenVanakar
Hi lbendlin
If we filter the data by calendar date, the numbers don't seem to match again. Is there a solution to this?
- lbendlin
Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.