Forum Discussion
Post PatronCreate relationship between two tables that do not have unique values
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.
Post Patronsevenhills thank you for the resource, you have actually modelled my data correctly. What I am trying to fugure out is - for example:
how many clients started service within the target fiscal year 2021-2022?
Normally, I would be able to get the data very easily by doing this:
intake test = COUNT(client[client id]) and then filtering for the year 2021-2022 using the column "start fiscal year" from my clients table. However, since target fiscal year is different that actual service start fiscal year, I want to be able to use the column "Target Fiscal Year" from the targets table to filter for 2021-2022 client start numbers. I hope this explanation make sense! Please let me know if you have any questions, thank you so much!
Super Userone 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.
- OPS-MLTSD
thank you so much! they both worked for me! Just wondering, what is the function of the TREATAS dax? this is the first time I am seeing this one.
You are right, it was a matter of multiple fact tables. To avoid that issue, I created a fiscal year dim table which I connected to both my clients and targets table to be able to filter for the data.
- sevenhills
Glad 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
thanks so much for the resource!