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.
We have a table with the project's name and the date when a part number for this project was added.
we can see that the dashboard for project 976 was created in August 2022
we can see that parts were added to this project, both before AND in the same month the dashboard was created
Solved! Go to Solution.
Hi @Anonymous
Final Solution is as follows
Count =
VAR CurrentDate =
MAX ( 'Date'[Date] )
VAR AllProjects =
FILTER ( Table1, Table1[Date created] <= current_date )
RETURN
VAR pj_type1 =
SUMX (
AllProjects,
VAR Parts1 =
FILTER (
CALCULATETABLE ( Table2, Table3[Date created] <= current_date ),
Table2[Added On] <> BLANK ()
)
VAR PartsAdded1 =
COUNTROWS ( Parts1 )
VAR LastAdded1 =
MINX ( Parts1, Table2[Added On] )
RETURN
IF ( PartsAdded1 > 0 && LastAdded1 <= CurrentDate, 1 )
)
VAR pj_type2 =
SUMX (
AllProjects,
VAR Parts2 =
FILTER (
CALCULATETABLE (
Table2,
USERELATIONSHIP ( Table3[Code], Table4[Code] ),
Table3[Date created] <= current_date
),
Table2[Added On] <> BLANK ()
)
VAR PartsAdded =
COUNTROWS ( Parts2 )
VAR LastAdded =
MINX ( Parts2, Table2[Added On] )
RETURN
IF ( PartsAdded2 > 0 && LastAdded2 <= CurrentDate, 1 )
)
RETURN
pj_type1 + pj_type2
Hi @Anonymous
Final Solution is as follows
Count =
VAR CurrentDate =
MAX ( 'Date'[Date] )
VAR AllProjects =
FILTER ( Table1, Table1[Date created] <= current_date )
RETURN
VAR pj_type1 =
SUMX (
AllProjects,
VAR Parts1 =
FILTER (
CALCULATETABLE ( Table2, Table3[Date created] <= current_date ),
Table2[Added On] <> BLANK ()
)
VAR PartsAdded1 =
COUNTROWS ( Parts1 )
VAR LastAdded1 =
MINX ( Parts1, Table2[Added On] )
RETURN
IF ( PartsAdded1 > 0 && LastAdded1 <= CurrentDate, 1 )
)
VAR pj_type2 =
SUMX (
AllProjects,
VAR Parts2 =
FILTER (
CALCULATETABLE (
Table2,
USERELATIONSHIP ( Table3[Code], Table4[Code] ),
Table3[Date created] <= current_date
),
Table2[Added On] <> BLANK ()
)
VAR PartsAdded =
COUNTROWS ( Parts2 )
VAR LastAdded =
MINX ( Parts2, Table2[Added On] )
RETURN
IF ( PartsAdded2 > 0 && LastAdded2 <= CurrentDate, 1 )
)
RETURN
pj_type1 + pj_type2
Hi @Anonymous
There is no information given about the relationships between the two tables therefore, I assume that Table1 filters Table2 (One to Many) relationship.
Please try
Count =
SUMX (
Table1,
VAR DateCreated = Table1[Dasboard Created]
VAR LastAdded =
CALCULATE ( MAX ( Table2[Part # was added on] ) )
VAR PartsAdded =
COUNTROWS ( CALCULATETABLE ( Table2 ) )
RETURN
IF ( PartsAdded > 0 && LastAdded <= DateCreated, 1 )
)
Yes, table 1 filters table 2. The measure works but does not return correct values just yet, I suspect it's because there are incorrect values in x axis date values in the chart
What date data should I put into the bar chart's X axis? Should it be the date when the dashboard was created?
@Anonymous
yes. The date when dashboard was created should work.
Unfortunately, it didn't work:( Maybe it's because of SUMX?
Because I need to count every distinct project that has part # added before or during the month its dashboard was created
@Anonymous
As per the sample data you have provided each row in Table1 represents one project. If this is not correct please let me know. The SUMX is just to have a correct result at the total level.
So, based on your expression it returns the count of 12 for the month of August:
After manually going through the tables, it is known that there should be 18 projects counted for the month of August, here is the list of screenshots for every project (for understanding):
1)
As you can see, project 1009 had its part added before or during the month the dashboard was created, so it should be counted for the month of August and the upcoming months (like cumulative)
2)
3)
4)
5)
6)
7)
😎
9)
10)
11)
12)
13)
14)
15)
16)
17)
18)
So, here they are - 18 projects that meet conditions and should be counted for August and so on (as a cumulative count).
BUT:
We have a project in which the dashboard was added in August, but parts # were added in September:
So, in August we have 18 projects, but in September we should have 19, this new project's parts were added in the next month - that's why we start counting it from September.
@Anonymous
One question, if you change the data type format to DD/MM/YYYY what DD to you get? Is there a difference between Dashboard Added Date and the Part Added Date?
Yes, there are differences. Both dashboards added date and part # added dates are datetimes, but since my granularity is month, I formatted them:
This is how they look without month formatting:
Hi @Anonymous
please try
Count =
VAR DateCreated =
EOMONTH ( SELECTEDVALUE ( Table1[Dasboard Created] ), 0 )
VAR ProjectsOnAndBefore =
CALCULATETABLE (
VALUES ( Table1[Projet Name] ),
ALL ( Table1[Dasboard Created] ),
Table1[Dasboard Created] <= DateCreated
)
RETURN
SUMX (
ProjectsOnAndBefore,
VAR LastAdded =
CALCULATE ( MAX ( Table2[Part # was added on] ) )
VAR PartsAdded =
COUNTROWS ( CALCULATETABLE ( Table2 ) )
RETURN
IF ( PartsAdded > 0 && LastAdded <= DateCreated, 1 )
)
It has counted 9 instead of 18 for August and didn't do cumulative count for subsequent months, i.e., the same project count wasn't returned for September or October. And it counted the project which has no part # added for some reason.
@Anonymous
Would you please provide a screenshot of your data modle? Do you have a date table?
Unfortunately, I can't share data model screenshots.
@Anonymous
I hope the relationship is based on the project name column?
please try the following otherwise, let me know if we can connect via teams or zoom
Count =
VAR DateCreated =
EOMONTH ( SELECTEDVALUE ( Table1[Dasboard Created] ), 0 )
VAR ProjectsOnAndBefore =
CALCULATETABLE (
VALUES ( Table1[Projet Name] ),
ALL ( Table1[Dasboard Created] ),
Table1[Dasboard Created] <= DateCreated
)
RETURN
SUMX (
ProjectsOnAndBefore,
VAR LastAdded =
CALCULATE (
MAX ( Table2[Part # was added on] ),
ALL ( Table1[Dasboard Created] )
)
VAR PartsAdded =
COUNTROWS ( CALCULATETABLE ( Table2, ALL ( Table1[Dasboard Created] ) ) )
RETURN
IF ( PartsAdded > 0 && LastAdded <= DateCreated, 1 )
)
//
Ok please share the meeting link
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 |
---|---|
43 | |
23 | |
21 | |
15 | |
15 |
User | Count |
---|---|
45 | |
31 | |
30 | |
18 | |
17 |