Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 category.
The clients and targets table do not have a relationship but they are both connected to the location table via location ID.
I have a table called clients table that looks like this:
client id | start fiscal year | service start date | service end fiscal year | end date | location ID | project number |
12345 | 2020-2021 | april 12, 2020 | 2021-2022 | April 12, 2021 | 3889A | 1 |
14562 | 2020-2021 | may 1, 2020 | 2021-2022 | may 1, 2021 | 3889A | 2 |
15678 | 2021-2022 | April 29, 2021 | 2022-2023 | April 20, 2022 | 5667A | 2 |
16785 | 2021-2022 | May 9, 2021 | 2022-2023 | May 1, 2022 | 58991B | 1 |
18765 | 2019-2022 | July 1, 2019 | 2022-2023 | April 2, 2022 | 58991B | 3 |
I have a targets table that looks like this:
Business ID | Target Fiscal Year | Target Category | Proposed amount of people | Location ID | Project Number |
44567 | 2021-2022 | Client Starts | 1300 | 3889A | 1 |
44567 | 2021-2022 | Client Exits | 1000 | 3889A | 1 |
44567 | 2020-2021 | Client Starts | 1200 | 5667A | 2 |
44567 | 2020-2021 | Client Exits | 900 | 5667A | 2 |
44567 | 2019-2020 | Client Starts | 800 | 3889A | 1 |
44567 | 2019-2020 | Client Exits | 600 | 3889A | 3 |
83726 | 2021-2022 | Client Starts | 1350 | 58991B | 1 |
and I have a location table that looks like this:
location ID | location Name |
58991B | 58991B |
3889A | 3889A |
5667A | 5667A |
I tried to create a relationship between clients table and targets table using the location ID but since both these columns do not contain unique values, it did not work. Then I tried to use the USERELATIONSHIP function to create a relationship between the two tables using the dax below:
Solved! Go to Solution.
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.
I tried creating relationship like this with your data samples
Note: Client is a unique list of Client_id, I created and not sure whether you need it or not.
Try these, as I was NOT sure from the post, which ones you need
intake test = COUNT(client[client id])
Testing Client in Targets =
CALCULATE( COUNT(Client[client id])
, CROSSFILTER(Targets[Location ID],Location[location ID], BOTH)
)
Clients Count =
var _l = SELECTEDVALUE(Targets[Location ID])
return CALCULATE( count(Client_Locations[client id]), FILTER(Client_Locations, Client_Locations[location ID] = _l))
Note: USERELATIONSHIP may not fit your scenario. https://docs.microsoft.com/en-us/dax/userelationship-function-dax
thank 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?
I 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.
@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!
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.
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.
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.
thanks so much for the resource!
can you send picture of your raltions ?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
77 | |
69 | |
52 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |