Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I am struggling to figure out how to create a calculated table based off two other related tables.
I have a date table for which i would like to use the month end date as a group and a secondary table of data where i would like to count the rows that fall within the month.
Ie:
table 1 (date table) | count of received in the month (table 2) | count of closed in the month (table 2) | count of assigned in the month (table 2) |
30/06/2022 | 50 | 60 | 45 |
31/07/2022 | 65 | 70 | 50 |
31/08/2022 | 40 | 20 | 30 |
30/09/2022 | 20 | 55 | 20 |
31/10/2022 | 35 | 40 | 25 |
The date table is linked to table two via the 'actual date' column that is joined to each received date, closed date and assigned date etc.
I know exactly how I would/could do this in SQL but DAX is ridiculous. Something so simple is seemingly impossible to me at this point...
Solved! Go to Solution.
Hi,
I am not sure how your datamodel looks like, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file.
I hope the below can provide some ideas on how to create a solution for your datamodel.
New Table =
ADDCOLUMNS (
DISTINCT ( 'Calendar'[End of Month] ),
"@Received",
COUNTROWS (
FILTER ( Data, EOMONTH ( Data[Received], 0 ) = 'Calendar'[End of Month] )
),
"@Closed",
COUNTROWS (
FILTER ( Data, EOMONTH ( Data[Closed], 0 ) = 'Calendar'[End of Month] )
),
"@Assigned",
COUNTROWS (
FILTER ( Data, EOMONTH ( Data[Assigned], 0 ) = 'Calendar'[End of Month] )
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi,
I am not sure how your datamodel looks like, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file.
I hope the below can provide some ideas on how to create a solution for your datamodel.
New Table =
ADDCOLUMNS (
DISTINCT ( 'Calendar'[End of Month] ),
"@Received",
COUNTROWS (
FILTER ( Data, EOMONTH ( Data[Received], 0 ) = 'Calendar'[End of Month] )
),
"@Closed",
COUNTROWS (
FILTER ( Data, EOMONTH ( Data[Closed], 0 ) = 'Calendar'[End of Month] )
),
"@Assigned",
COUNTROWS (
FILTER ( Data, EOMONTH ( Data[Assigned], 0 ) = 'Calendar'[End of Month] )
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
@moniqueg , You need a date table, Active/inactive joined and userelationship
refer this approch
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |