DAX - incorrect count when applying filters on dashboard

[Jon von der Heyden]

Hi

I have a table, key fields being:

• retailer_name
• order_ref_id
• order_date
• payer_status

I have the following DAX measure:

Perc Orders By Payer Status by Retailer =
var SelectedOrders = COUNT(conversion_loan_collection_order[order_ref_id])
var SelectedRetailerOrders = CALCULATE(COUNT(conversion_loan_collection_order[order_ref_id]), ALL(conversion_loan_collection_order[payer_status]))
return
SelectedOrders/SelectedRetailerOrders

I have a dashboard that shows the measure result by retailer_name (across rows) and payer_status (across columns).

It appears to work OK with no filters on the dashboard.

Then I have an order_date slicer having selected 01/01/2021 - 30/09/2021 (dd/mm/yyyy)
And I have selected a single retailer in another slicer (lets call this RetailerA)

I can observe the following correct results:

SelectedretailerOrders = 636, of which payer_status:
always late : 4
always pays : 452
escape : 99
never pays : 21
sometimes late: 39
sometimes pays: 21

I have validated these order_ref_id counts by manually applying the retailer_name and order_date filters to the dataset.

The problem is that I also have a slicer on the dashboard for payer_status. If I select only 'never pays', it displays a result of 116, instead of the correct 21.

I can't even figure out a count of 116.

Can anyone suggest what is wrong?

 

[Jon von der Heyden]

Hi Rory

With the disconnected table removed, the order_date slicer paired back to Table1 and this measure:

Perc Orders By Payer Status by Retailer =
var SelectedOrders = COUNT(Table1[order_ref_id])
var SelectedRetailerOrders = CALCULATE(COUNT(Table1[order_ref_id]), ALL(Table1[payer_status]))
return
SelectedOrders & "/" & SelectedRetailerOrders

* Note I have concatenated both operands of the division for illustration purposes.

Wih no filters applied you should see this:

Now if we apply an order_date filter of 03/01/2021 - 18/01/2021 we get this:

At this point we know that the denominator should always be 16.

The problem is visible if we now also add a filter on payer_status, e.g. "Never paid":

Note that the denominator for each retailer is incorrect (i.e. not 16).

Hope I'm making sense?

No worries if this is a pain. The disconnected table solution works, it's just that I am frustrated that I don't understand this behaviour.

Cheers

 

[RoryA]

OK I do see it now. I was concentrating on the status slicer and didn't notice that the date slicer had disappeared, and that's the one causing the issues as far as I can tell (although your issue seems to have switched from the numerator to the denominator). Using any combination of the status and retailer name slicers seems to work fine, but as soon as the order date slicer is actually used (in combination with any other filter), it messes everything up.

 

[Jon von der Heyden]

Exactly, you got it. So I'm not going crazy.

 

[RoryA]

So I'm not going crazy

No comment.

I've seen a couple of similar issues online, but no real explanation. Have you tried adding a proper date table and using that for the slicer?

 

[Jon von der Heyden]

Huh! That worked!

So I have a new table:
Order Dates = ALL(Table1[Order_date])

And I have a relationship between [Table1] and [Order Dates]

And then I swapped out the slicer.

And the original DAX measure works. Buuuut.... It doesn't work on my original data set, yet the long-winded disconnected table approach does work on the original data set.

I'm very confused!

 

[Jon von der Heyden]

Actually, it does work, just me making silly mistakes again. I'm going to mark that as the solution (because it does solve the problem, much neater than the other approach)., although I confess I still don't understand why PBI didn't like the initial configuration. Thanks Rory!