Filters created using calculated table does not give the desired result - Need Help

[AmeenVanakar]

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.

[HotChilli · Accepted solution]

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.

[lbendlin · Accepted solution]

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.

[AmeenVanakar]

The 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).

Report.pbix

[lbendlin]

This is a calculated column that is not impacted by the filters in your data model.

You need to use a measure instead.

[HotChilli · Accepted solution]

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.

[AmeenVanakar]

Hi @HotChilli 

Thanks. This makes sense.