Forum Discussion
Solved
Direct Query help
Hi, So I am using a Direct Query to bring in some inspection information for catch basins. Basically I have two tables; one with all of our catchbasins (id of each one = FACILITYID) and another...
OMG! I finally go this to work. So I added 3 measures; 1 for CatchBasin count (CB_Count), 1 for the Inspection count (INSP_Count) and 1 to calculate the % completed (PercentComplete).
CB_Count = calculate(COUNT(CatchBasin_Inspections[FACILITYID]))INSP_Count = CALCULATE(DISTINCTCOUNTNOBLANK(CatchBasin_Inspections[MaxOfINSP_DATE]), FILTER(CatchBasin_Inspections, CatchBasin_Inspections[MaxOfINSP_DATE] >= date(2021, 01, 01)))PercentComplete = calculate(DIVIDE(CatchBasin_Inspections[INSP_Count],CatchBasin_Inspections[CB_Count],0)*100)Figuring out the FILTER on the INSP_Count part was the tricky part, but seems to solve the issue of only looking for dates within a specific time period. I'll have to update that to not include anything beyond the end of 2022, but now it is good.I also placed a slicer so that the user can select the service area and see the % completed within the specified area.This only took me about 3 days!! LOL
OMG! I finally go this to work. So I added 3 measures; 1 for CatchBasin count (CB_Count), 1 for the Inspection count (INSP_Count) and 1 to calculate the % completed (PercentComplete).
CB_Count = calculate(COUNT(CatchBasin_Inspections[FACILITYID]))
INSP_Count = CALCULATE(DISTINCTCOUNTNOBLANK(CatchBasin_Inspections[MaxOfINSP_DATE]), FILTER(CatchBasin_Inspections, CatchBasin_Inspections[MaxOfINSP_DATE] >= date(2021, 01, 01)))
PercentComplete = calculate(DIVIDE(CatchBasin_Inspections[INSP_Count],CatchBasin_Inspections[CB_Count],0)*100)
Figuring out the FILTER on the INSP_Count part was the tricky part, but seems to solve the issue of only looking for dates within a specific time period. I'll have to update that to not include anything beyond the end of 2022, but now it is good.
I also placed a slicer so that the user can select the service area and see the % completed within the specified area.
This only took me about 3 days!! LOL