The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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?
Solved! Go to 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,
...
)
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,
...
)
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?
Your variables are tables. DAX knows the lineage of their columns.
If you want you can make the link between facts and practices bidirectional.
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.
Consider using TREATAS() or (shudder) LOOKUPVALUE()
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
99 | |
81 | |
62 | |
54 |
User | Count |
---|---|
249 | |
119 | |
115 | |
94 | |
70 |