Convert Calculated Table to measure

[Anonymous]

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

[v-yingjl · Accepted solution]

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) * 100

UseStatus = 
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 Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

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