Count of dimension with common attribute in other dimensions considering filter context

[fabiuzz]

Hi guys, I've a doubt about the best practice in a case like the one below.

The datamodel is obviously more complex than this, but the core question is summarized here and is about the dimensions "Customers", "Sercives" and "Practices".

In "Practices" there are reference to customer and services (in other terms 1 practices reference 1 customer, 1 service). Actually there are other dimension and common attributes (7-8), but here I've made a semplification with only 2.

I want to count "Practices" using only the common attributes present in filter context as a filter of "Practices" dimension.

I've found two ways to solve:

1. Handling all in measure

Practices (Filtered) = 
VAR SelectedCustomers = VALUES(Customers[K_Customer])
VAR SelectedServices = VALUES(Services[K_Service])
VAR ... = VALUES(...)

RETURN
CALCULATE(
    COUNTROWS(Practices),
    Practices[K_Customer] IN SelectedCustomers,
    Services[K_Service] IN SelectedServices,
    ...[...] IN ...
)

2. Create a Bridge/FakeFact table with every single possibile K_ related to common dimension

and then create a simple measure with DISTINCTCOUNT (or SUMMARIZE + COUNTROWS)

Practices (Filtered) V2 = DISTINCTCOUNT(PractiesFakeFact[K_Pratice])

Which one you think is the best in terms of readability? The first one could have performance problem?

Other suggestion?

[lbendlin · Accepted solution]

Go with the first one.  Remember that filter context is a list of values.  You can simplify your measure

Practices (Filtered) = 
VAR SelectedCustomers = VALUES(Customers[K_Customer])
VAR SelectedServices = VALUES(Services[K_Service])
VAR ... = VALUES(...)

RETURN
CALCULATE(
    COUNTROWS(Practices),
    SelectedCustomers,
    SelectedServices,
    ...
)

[fabiuzz]

Thank you @lbendlin but I dont' understand how it could work with your statement... I reply here cause maybe the approach is different from the one you think is the best one.

Customer dimension is not related with Practices dimension and I need to filter Practices[K_Customer] with a list of values of Customer[K_Customer] coming from filter context .

The original statement (there was a little mistake in the text... here below the correct one):

Practices (Filtered) = 
VAR SelectedCustomers = VALUES(Customers[K_Customer])
VAR SelectedServices = VALUES(Services[K_Service])
VAR ... = VALUES(...)

RETURN
CALCULATE(
    COUNTROWS(Practices),
    Practices[K_Customer] IN SelectedCustomers,
    Practices[K_Service] IN SelectedServices,
    ...[...] IN ...
)

I think is different from your:

Practices (Filtered) = 
VAR SelectedCustomers = VALUES(Customers[K_Customer])
VAR SelectedServices = VALUES(Services[K_Service])
VAR ... = VALUES(...)

RETURN
CALCULATE(
    COUNTROWS(Practices),
    SelectedCustomers,
    SelectedServices,
    ...
)

How DAX CALCULATE could know which columns use to apply the filters in Practices dimension?

[lbendlin]

Your variables are tables. DAX knows the lineage of their columns.

If you want you can make the link between facts and practices bidirectional.

[fabiuzz]

I can't use bidirectional relationship, the model has 3 facts and many dimension, I will broke some logic in other points. Furthermore the fact doesn't have all the istance of practices/services/customer... is a fact. In very particular situation should also be empty.

There's no direct relationship between Practices and Customer, your formula retruns the total rows of Practices. It will work only with bidirectional or with a countrows on the fact... but it's not the aim of the question.

[lbendlin]

Consider using TREATAS() or (shudder) LOOKUPVALUE()