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 for the information.
I tried to recreate your data and I am still not able to establish a relationship between the clients and targets table.
I was wondering, would it be possible to modify this dax like this? Because I need to know for example clients starts for a particular target fiscal year, can you help me modify my dax below?
clients[Start fisca year] = targets[target fiscal year] AND clients[Location id] = targets [location ID]
Super UserI am not sure what do you mean by "I tried to recreate your data and I am still not able to establish a relationship between the clients and targets table. "
----------------------------------------------------------
Also, Is this what you are looking? based on my modeling diagram above ...
Testing Client in Targets 2 =
var _Target_FY = SELECTEDVALUE(Targets[Target Fiscal Year])
RETURN CALCULATE( COUNT(Client_Locations[client id])
, Filter ( Targets, Targets[Target Category] = "Client Starts")
, CROSSFILTER(Targets[Location ID],Location[location ID], BOTH)
, Filter(Client_Locations, Client_Locations[start fiscal year] = _Target_FY)
)
----------------------------------------------------------
Could you share your data screens and model diagram? it is tough to visualize.
- OPS-MLTSD
sevenhills 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!- sevenhills
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.
- 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.