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 Patronthank 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.
Super UserGlad 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!