Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

DateCounty IDSubCategoryIdCategoryIdUserFacilityId
5/26/2020E006911SL-AE01164
5/26/2020E006931SL-AE02164
5/26/2020E006931SL-AE03164
5/26/2020E006911SL-AE04164
5/26/2020E006911SL-AE05164
5/26/2020E006922SL-AE06164
5/26/2020E006922SL-AE07164
5/27/2020E006911SL-AE01164
5/27/2020E006911SL-AE08164
5/27/2020E006922SL-AE06164
5/27/2020E006922SL-AE07164
5/26/2020E001011SL-E05165
5/26/2020E001022SL-E06165
5/26/2020E001022SL-E07165
5/27/2020E001022SL-E06165
5/28/2020E001022SL-E07165
5/26/2020E001131SL-08165
5/27/2020E001122SL-09165
5/28/2020E001131SL-08165

 

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 IdPCCount
E006950
E001030
E00115

 

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

2 Replies

  • v-yingjl's avatar
    v-yingjl
    Community 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) * 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's avatar
      Anonymous
      Not 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