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.
I have a Table ('Table') with two Projects (ABC-10 and ABC-11) where an Project can occur over multiple dates due to having different Sub Project states and Companies.
Project | Sub Project | Company | Date |
ABC-10 | 1 | A | 30/09/2021 |
ABC-10 | 2 | A | 30/09/2021 |
ABC-10 | 1 | B | 30/06/2022 |
ABC-10 | 1 | C | 30/09/2021 |
ABC-10 | 1 | D | 30/09/2021 |
ABC-10 | 2 | D | 30/08/2021 |
ABC-11 | 1 | A | 30/09/2021 |
ABC-11 | 2 | A | 30/09/2021 |
ABC-11 | 1 | B | 30/06/2022 |
ABC-11 | 1 | C | 30/09/2021 |
ABC-11 | 1 | D | 30/09/2021 |
ABC-11 | 2 | D | 30/01/2020 |
What I need:
- I would like to create one measure ([MyMeasure]) to distinct count the Project by the first Date at wich project occurred.
- I pretend that [MyMeasure] provides me the same approach based on the companies I select.
- This measure is needed for Bars Charts and table visuals.
This is the expected result if I select all companies:
Date | MyMeasure |
30/01/2020 | 1 |
30/08/2021 | 1 |
This is the expected result if I select the companies A and B :
Date | MyMeasure |
30/06/2022 | 2 |
I already create one measure wich provides me the right value if I don't select any company.
MyMeasure =
VAR t =
CALCULATETABLE(
VALUES('Table'[Project])
,FILTER(
'table'
,VAR dateInRow=[Date]
RETURN
dateInRow=CALCULATE(
min('Table'[Date])
,ALLEXCEPT('Table','Table'[Project]))))
var result = COUNTROWS(t)
return
result
The problem is when I select one or more companies (A and B in this example) the maesure doesn't calculate the right value:
How can I solve this?
Thank you!
Solved! Go to Solution.
Hi @Anonymous
I produced the following measure and I would say it works. What I do not get is why you expect 30/06/2022 when choosing A and B. WOuldnt it be 30/09/2021?
Here my measure: SampleM99106 is the name of your table
MyMeasure =
var __FirstDateOfProject =
CALCULATETABLE(
SUMMARIZE(SampleM99106,[Project],"FirstDate",FIRSTDATE(SampleM99106[Date])),
ALLEXCEPT(SampleM99106,SampleM99106[Project],SampleM99106[Company])
)
var __SelectedDate = SELECTEDVALUE(SampleM99106[Date])
var __Result =
COUNTROWS(
FILTER(__FirstDateOfProject,[FirstDate]=__SelectedDate)
)
Return
__Result
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
-----------------------------------------------------
Hi @Anonymous
I produced the following measure and I would say it works. What I do not get is why you expect 30/06/2022 when choosing A and B. WOuldnt it be 30/09/2021?
Here my measure: SampleM99106 is the name of your table
MyMeasure =
var __FirstDateOfProject =
CALCULATETABLE(
SUMMARIZE(SampleM99106,[Project],"FirstDate",FIRSTDATE(SampleM99106[Date])),
ALLEXCEPT(SampleM99106,SampleM99106[Project],SampleM99106[Company])
)
var __SelectedDate = SELECTEDVALUE(SampleM99106[Date])
var __Result =
COUNTROWS(
FILTER(__FirstDateOfProject,[FirstDate]=__SelectedDate)
)
Return
__Result
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
-----------------------------------------------------
Hey,
This approach works! Thank you!
Yes, sorry, my bad. The correct return for A and B companies is two projects on 30/09/2021.
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 |
---|---|
41 | |
19 | |
19 | |
15 | |
15 |
User | Count |
---|---|
49 | |
26 | |
22 | |
17 | |
16 |