Forum Discussion
Convert Calculated Table to measure
Hi,
I've created a calculated table based on another calculated table as a temp solution as i wasn't able to create a measure to get the expected output. I am wondering, if this could be rather converted to a measure
I have a calculated table "CountrsPerDay" which has data grouped by day, countyId, SubCategoryId, categoryId, user, facilityId like below
| Date | County ID | SubCategoryId | CategoryId | User | FacilityId |
| 5/26/2020 | E0069 | 1 | 1 | SL-AE01 | 164 |
| 5/26/2020 | E0069 | 3 | 1 | SL-AE02 | 164 |
| 5/26/2020 | E0069 | 3 | 1 | SL-AE03 | 164 |
| 5/26/2020 | E0069 | 1 | 1 | SL-AE04 | 164 |
| 5/26/2020 | E0069 | 1 | 1 | SL-AE05 | 164 |
| 5/26/2020 | E0069 | 2 | 2 | SL-AE06 | 164 |
| 5/26/2020 | E0069 | 2 | 2 | SL-AE07 | 164 |
| 5/27/2020 | E0069 | 1 | 1 | SL-AE01 | 164 |
| 5/27/2020 | E0069 | 1 | 1 | SL-AE08 | 164 |
| 5/27/2020 | E0069 | 2 | 2 | SL-AE06 | 164 |
| 5/27/2020 | E0069 | 2 | 2 | SL-AE07 | 164 |
| 5/26/2020 | E0010 | 1 | 1 | SL-E05 | 165 |
| 5/26/2020 | E0010 | 2 | 2 | SL-E06 | 165 |
| 5/26/2020 | E0010 | 2 | 2 | SL-E07 | 165 |
| 5/27/2020 | E0010 | 2 | 2 | SL-E06 | 165 |
| 5/28/2020 | E0010 | 2 | 2 | SL-E07 | 165 |
| 5/26/2020 | E0011 | 3 | 1 | SL-08 | 165 |
| 5/27/2020 | E0011 | 2 | 2 | SL-09 | 165 |
| 5/28/2020 | E0011 | 3 | 1 | SL-08 | 165 |
i created 2 more calculated tables using the data above with the following dax to get status for each facility and another calculated table to get status for each county (below)
SummaryByGS
VAR CombinedTable = ADDCOLUMNS(CountsPerDay, "PCsCount", RELATED('PCData'[PCCount]))
RETURN
ADDCOLUMNS(
CALCULATETABLE(
SUMMARIZE(
CombinedTable,
CountsPerDay[date],
CountsPerDay[countyId],
"TotalPCCount",MIN('PCData'[PCCount]),
"TotalUsers", CALCULATE(COUNT(CountsPerDay[TUser]), FILTER(CountsPerDay, CountsPerDay[CategoryId] = 2))
),
FILTER(CategoryCountsPerDay, CountsPerDay[CategoryId] = 2)
) ,
"PercentUse", DIVIDE([TotalUsers],[TotalPCCount])*100.0, //Validation
"UseStatus", SWITCH(
TRUE(),
DIVIDE([TotalHosts],[TotalPCCount])*100.0 <= 1 || [TotalPCCount] <=5, "None",
DIVIDE([TotalHosts],[TotalPCCount])*100.0 <= 30, "Minimal",
DIVIDE([TotalHosts],[TotalPCCount])*100.0 > 30, "Regular"
)
)
PCData Table is related via County Id, It has PCCounts by CountyId
| County Id | PCCount |
| E0069 | 50 |
| E0010 | 30 |
| E0011 | 5 |
The final result is displayed in tables and bar stack visuals showing different status and percentage values;
filters used - CountyId, FacilityId, Date (one day or range of dates)
This is a large dataset with 4+Millions rows and growing.. So, i would appreciate, if any1 here could help me convert these calculated tables to a measure
Hi Anonymous ,
Maybe like this? Nor certain and your formula seems not match with the field in the table.
Percentage = var TotalPCCount = MIN('PCData'[PCCount]) var TotalUsers = CALCULATE ( COUNT ( CountsPerDay[User] ), FILTER ( CountsPerDay, CountsPerDay[CategoryId] = 2 ) ) return DIVIDE(TotalUsers,TotalPCCount) * 100UseStatus = var TotalPCCount = MIN('PCData'[PCCount]) var TotalUsers = CALCULATE ( COUNT ( CountsPerDay[User] ), FILTER ( CountsPerDay, CountsPerDay[CategoryId] = 2 ) ) return SWITCH ( TRUE (), DIVIDE ( TotalUsers, TotalPCCount ) * 100.0 <= 1 || TotalPCCount <= 5, "None", DIVIDE ( TotalUsers, TotalPCCount ) * 100.0 <= 30, "Minimal", DIVIDE ( TotalUsers, TotalPCCount ) * 100.0 > 30, "Regular" )Or you can consider sharing a dummy sample .pbix file for further discussion.
Best Regards,
Yingjie LiIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
- v-yingjlCommunity Support
Hi Anonymous ,
Maybe like this? Nor certain and your formula seems not match with the field in the table.
Percentage = var TotalPCCount = MIN('PCData'[PCCount]) var TotalUsers = CALCULATE ( COUNT ( CountsPerDay[User] ), FILTER ( CountsPerDay, CountsPerDay[CategoryId] = 2 ) ) return DIVIDE(TotalUsers,TotalPCCount) * 100UseStatus = var TotalPCCount = MIN('PCData'[PCCount]) var TotalUsers = CALCULATE ( COUNT ( CountsPerDay[User] ), FILTER ( CountsPerDay, CountsPerDay[CategoryId] = 2 ) ) return SWITCH ( TRUE (), DIVIDE ( TotalUsers, TotalPCCount ) * 100.0 <= 1 || TotalPCCount <= 5, "None", DIVIDE ( TotalUsers, TotalPCCount ) * 100.0 <= 30, "Minimal", DIVIDE ( TotalUsers, TotalPCCount ) * 100.0 > 30, "Regular" )Or you can consider sharing a dummy sample .pbix file for further discussion.
Best Regards,
Yingjie LiIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
- AnonymousNot applicable
Thank You! Exactly what i was looking for but the requirement changed. I need to calculate percentile over a period of time now and for some reason percentilex isn't working correctly as a measure. So, i have to go back to the calculated table and use the counts in the measure to calculate percentile