Forum Discussion
OPS-MLTSD
Post Patron
4 years agoCreate relationship between two tables that do not have unique values
Hello, I have 3 tables - clients, targets, and location. I am trying to count how many clients started and finished services within a certain target fiscal year and in a certain target catego...
- 4 years ago
one more attempt... see which of these measures work for your scenario.
Measure 3 = var _l = SELECTEDVALUE(Targets[Location ID]) var _FY = SELECTEDVALUE(Targets[Target Fiscal Year]) RETURN CALCULATE( count( Client_Locations[client id]) , Filter(Targets, Targets[Target Category] = "Client Starts" ) , Client_Locations[location ID] = _l , Client_Locations[start fiscal year] = _FY )Measure = CALCULATE( count( Client_Locations[client id]) , Filter(Targets, Targets[Target Category] = "Client Starts") , TREATAS ( SUMMARIZE(Targets, Targets[Location ID], Targets[Target Fiscal Year]) , Client_Locations[location ID], Client_Locations[start fiscal year] ) )You are doing is common scenario when you have multiple fact tables and getting related counts based on business needs.
sevenhills
Super User
4 years agoGlad it worked. Having lookup / common-unique / dim tables in the model always better and easy.
You can read about TREATAS here: https://dax.guide/treatas/
In my own terms, by using TREATAS basically saying the columns (data) we are providing from one table is same as in other table columns.
OPS-MLTSD
Post Patron
4 years agothanks so much for the resource!