%3CLINGO-SUB%20id%3D%22lingo-sub-1278946%22%20slang%3D%22en-US%22%3ERe%3A%20Mapping%20External%20Users%E2%80%99%20Locations%20with%20CQD%20Data%20and%20PowerBI%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1278946%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F50347%22%20target%3D%22_blank%22%3E%40Siunie%20Sutjahjo%3C%2FA%3E%26nbsp%3BThanks%20for%20documenting%20this.%20This%20will%20be%20very%20helpful%20to%20monitor%20and%20take%20call%20quality%20to%20next%20level.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1282514%22%20slang%3D%22en-US%22%3ERe%3A%20Mapping%20External%20Users%E2%80%99%20Locations%20with%20CQD%20Data%20and%20PowerBI%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1282514%22%20slang%3D%22en-US%22%3E%3CP%3EIs%20there%20a%20way%20to%20ensure%20Excel%20opens%20the%20entire%20CSV%20file...on%20Win10%20%26amp%3B%20macOS%2C%20it%20stops%20loading%20content%20past%201%2C048%2C576%20rows...stops%20at%2C%20roughly%2C%20the%20Netherlands.%26nbsp%3B%20Should%20I%20break%20the%20CSV%20file%20into%20chunks%20and%20process%20them%20separately%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1289096%22%20slang%3D%22en-US%22%3ERe%3A%20Mapping%20External%20Users%E2%80%99%20Locations%20with%20CQD%20Data%20and%20PowerBI%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1289096%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F609920%22%20target%3D%22_blank%22%3E%40MartySchlacter%3C%2FA%3E%26nbsp%3B-%20you%20can%20try%20to%20open%20it%20directly%20in%20PBI%20using%20a%20new%20column%20-%20here%20is%20for%20your%20reference%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CDIV%3EReflexive1%20%3D%20CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE(INT('IP2LOCATION-LITE-DB3'%5BStartDecimal%5D%2F(256*256*256))%2C%22.%22)%2CINT('IP2LOCATION-LITE-DB3'%5BStartDecimal%5D%2F(256*256))-INT('IP2LOCATION-LITE-DB3'%5BStartDecimal%5D%2F(256*256*256))*256)%2C%22.%22)%2CINT('IP2LOCATION-LITE-DB3'%5BStartDecimal%5D%2F(256))-INT('IP2LOCATION-LITE-DB3'%5BStartDecimal%5D%2F(256*256))*256)%2C%22.0%2F24%22)%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%20to%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1984%22%20target%3D%22_blank%22%3E%40Nirmal%20Sundara%20Raman%3C%2FA%3E%26nbsp%3Bwho%20has%20tried%20it%20directly%20in%20PBI%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1291843%22%20slang%3D%22en-US%22%3ERe%3A%20Mapping%20External%20Users%E2%80%99%20Locations%20with%20CQD%20Data%20and%20PowerBI%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1291843%22%20slang%3D%22en-US%22%3E%3CP%3EOn%20step%204%20of%20the%20sheet%2C%20I%20see%20column%20G%20done%20but%20on%20step%203%20of%20importing%20into%20Power%20BI%20there%20is%20two%20columns%20around%20start%20ip%20and%20end%20ip%20with%20the%20last%20octet%20missing%20(column%207%20and%208)%3C%2Fimg%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20don't%20seem%20to%20be%20able%20to%20get%20to%20that%20place%20so%20suggests%20some%20manual%20work%20was%20done%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ecan%20anyone%20advise%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1277627%22%20slang%3D%22en-US%22%3EMapping%20External%20Users%E2%80%99%20Locations%20with%20CQD%20Data%20and%20PowerBI%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1277627%22%20slang%3D%22en-US%22%3E%3CP%3EWith%20the%20global%20crisis%20of%20COVID-19%20and%20the%20impact%20on%20the%20global%20workforce%2C%20the%20ability%20to%20see%20utilization%20metrics%20from%20an%20external%20standpoint%20(for%20example%3A%20a%20user%20working%20from%20home)%20has%20become%20increasingly%20important%20for%20Teams%20IT%20Admins.%3C%2FP%3E%0A%3CP%3ETo%20see%20network%20information%20broken%20down%20between%20internal%2Fexternal%20networks%20and%20to%20take%20full%20advantage%20of%20the%20Advanced%20Call%20Quality%20Dashboard%20(CQD)%20data%2C%20Microsoft%20highly%20recommends%20that%20a%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2FMicrosoftTeams%2Fturning-on-and-using-call-quality-dashboard%23building-data-file%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EBuilding%20Data%20File%3C%2FA%3E%20be%20created%20and%20uploaded.%20Out%20of%20the%20many%20reasons%20for%20uploading%20the%20data%20file%2C%20one%20key%20piece%20of%20information%20relied%20upon%20is%20this%20delineation%20between%20internal%20networks%20(deployed%20and%20managed%20by%20organization%20IT%20admin)%20and%20external%20networks%20(managed%20by%20end-users%20or%20a%20private%20entity%2C%20such%20as%3A%20coffee%20shops).%26nbsp%3B%20Knowing%20the%20call%20volume%2C%20call%20quality%20%26amp%3B%20reliability%20on%20the%20organization%20network%20(wired%20or%20WiFi)%20is%20important%2C%20as%20this%20is%20within%20the%20organizations%20sphere%20of%20control.%20Uploading%20a%20building%20data%20will%20expedite%20the%20organization%20IT%20admin%20in%20locating%20where%20problems%20occur%20in%20a%20specific%20region%2Fsite%2Flocation%2Fbuilding%2Ffloors%20of%20interest%20during%20call%20quality%20and%20reliability%20troubleshooting.%20External%20networks%20are%20managed%20by%20home%20users%20or%20private%20entity%20(such%20as%20a%20coffee%20shop%20owner).%20There%20is%20little%20action%20to%20occur%20on%20this%20front%2C%20other%20than%20providing%20recommended%20connectivity%20practices%20to%20the%20end%20users.%3C%2FP%3E%0A%3CP%3EIn%20keeping%20with%20the%20spirit%20of%20%E2%80%9CEmpowering%20every%20person%20and%20organization%20on%20the%20planet%20to%20achieve%20more%E2%80%9D%20Microsoft%20has%20undertaken%20an%20effort%20to%20help%20those%20organizations%20currently%20lacking%20a%20building%20data%20file.%20By%20leveraging%20a%20third-party%20ISP%2FIP%20location%20database%20in%20conjunction%20with%20the%20reflexive%20IP%20address%20associated%20with%20a%20user%2C%20an%20approximate%20location%20can%20be%20determined%20to%20differentiate%20internal%20and%20external%20connectivity.%20Do%20keep%20in%20mind%20that%20the%20published%2Fdownloadable%20%3CA%20href%3D%22https%3A%2F%2Fgithub.com%2FMicrosoftDocs%2FOfficeDocs-SkypeForBusiness%2Fblob%2Flive%2FTeams%2Fdownloads%2FCQD-Power-BI-query-templates.zip%3Fraw%3Dtrue%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EMicrosoft%20Call%20Quality%20Power%20BI%20connector%20and%20associated%20Power%20BI%20templates%3C%2FA%3E%20work%20best%20when%20building%20data%20is%20provided.%3C%2FP%3E%0A%3CP%3E%3CSPAN%3ETo%20visualize%20the%20locations%20of%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3Ehome%20users%20in%20CQD%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%26nbsp%3BPower%20BI%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%2C%20follow%20the%20steps%20below.%20This%20guidance%20will%20help%20identify%20the%20locations%20and%20ISP%20providers%20via%203%3C%2FSPAN%3E%3CSPAN%3E%3CSUP%3Erd%3C%2FSUP%3E%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3Bparty%20Geo%20IP%20databases.%26nbsp%3B%20%3CSTRONG%3EThis%20example%20is%20provided%20for%20instructional%20purposes.%26nbsp%3B%26nbsp%3B%3C%2FSTRONG%3EIt%20is%20up%20to%20you%20to%20follow%20any%20laws%20or%20regulations%20that%20might%20apply%20to%26nbsp%3Bmapping%20locations%20of%20your%20users%20based%20on%20their%20IP%20addresses.%3C%2FSPAN%3E%3CSPAN%3E%3CSTRONG%3E%26nbsp%3B%3C%2FSTRONG%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22wfh17.jpg%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F183163i9EA2AEDD124CB958%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22wfh17.jpg%22%20alt%3D%22wfh17.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20size%3D%225%22%20color%3D%22%23993366%22%3E%3CSTRONG%3EDownload%20IP%20addresses%20from%20an%20IP%20address%20database%3C%2FSTRONG%3E%3CSPAN%20data-contrast%3D%22none%22%3E%3CSTRONG%3E.%3C%2FSTRONG%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%20data-ccp-props%3D%22%7B%26quot%3B201341983%26quot%3B%3A0%2C%26quot%3B335559738%26quot%3B%3A40%2C%26quot%3B335559739%26quot%3B%3A0%2C%26quot%3B335559740%26quot%3B%3A259%7D%22%20data-wac-het%3D%221%22%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20data-contrast%3D%22auto%22%3EIf%20your%20organization%20already%20has%20a%20subscription%20to%20an%20IP%20Geolocation%20Database%2C%20this%20may%20be%20used%20if%20it%20has%20the%20required%20information%20in%20it.%20%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EIf%20not%2C%20search%20for%20an%20IP%20Geolocation%20Database%20and%20download%20it.%20This%20may%20require%20signing%20up%20for%20an%20account%20with%20the%20third-party%20providing%20the%20database.%3C%2FP%3E%0A%3CP%3EOne%20such%20provider%20is%20%3CA%20href%3D%22https%3A%2F%2Flite.ip2location.com%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Flite.ip2location.com%3C%2FA%3E%3CSPAN%3E.%20%3C%2FSPAN%3E%3CSPAN%3EBut%20you%20could%20download%20similar%20data%20from%20any%20other%20database%20for%20other%20reasons.%26nbsp%3B%20%3CSTRONG%3ENote%20that%20the%20accuracy%20of%20your%20results%20will%20depend%20on%20the%20precision%20of%20the%20IP-to-location%20data%20set.%3C%2FSTRONG%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EAfter%20extracting%20the%20contents%20from%20the%20ZIP%20file%2C%20open%20the%20CSV%20file%20to%20see%20contents%20like%20the%20screenshot%20below.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22wfh01.jpg%22%20style%3D%22width%3A%20975px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F181799i4AE9BF6CE5A8438D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22wfh01.jpg%22%20alt%3D%22wfh01.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CEM%3ENote%3A%20Query%20times%20may%20be%20optimized%20by%20filtering%20out%20extraneous%20data%20from%20the%20CSV.%20For%20example%2C%20if%20your%20organization%20only%20operates%20out%20of%20the%20United%20States%2C%20all%20other%20countries%20can%20be%20excluded.%3C%2FEM%3E%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId--1381158190%22%20id%3D%22toc-hId--1381158190%22%20id%3D%22toc-hId--1381158190%22%20id%3D%22toc-hId--1381158190%22%20id%3D%22toc-hId--1381158190%22%20id%3D%22toc-hId--1381158190%22%20id%3D%22toc-hId--1381158190%22%20id%3D%22toc-hId--1381158190%22%20id%3D%22toc-hId--1381158190%22%20id%3D%22toc-hId--1381158190%22%20id%3D%22toc-hId--1381158190%22%20id%3D%22toc-hId--1381158190%22%20id%3D%22toc-hId--1381158190%22%20id%3D%22toc-hId--1381158190%22%20id%3D%22toc-hId--1381158190%22%3E%3CFONT%20size%3D%225%22%20color%3D%22%23993366%22%3EIP%20Database%20Preparation%3C%2FFONT%3E%3C%2FH2%3E%0A%3CP%3EColumns%20A%20and%20B%20in%20the%20above%20example%20are%20the%20IP%20ranges%20(start%20and%20end)%20represented%20in%20a%20decimal%20format.%20These%20do%20not%20look%20like%20traditional%20IP%20addresses%20and%20will%20need%20to%20be%20converted%20to%20a%20more%20recognizable%20format.%3C%2FP%3E%0A%3CP%3EThe%20formula%20to%20use%20in%20this%20instance%20is%20as%20follows%3A%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%23808080%22%3E%3CEM%3E%3D%20CONCATENATE(INT(A1%2F(256*256*256))%2C%22.%22%2CINT(A1%2F(256*256))-INT(A1%2F(256*256*256))*256%2C%22.%22%2CINT(A1%2F(256))-INT(A1%2F(256*256))*256%2C%22.0%2F24%22)%3C%2FEM%3E%3C%2FFONT%3E%3C%2FP%3E%0A%3COL%3E%0A%3CLI%3ECopy%20formula%20from%20the%20equal%20sign%20to%20the%20end%20of%20the%20line.%3CBR%20%2F%3E%3D%20CONCATENATE(INT(A1%2F(256*256*256))%2C%22.%22%2CINT(A1%2F(256*256))-INT(A1%2F(256*256*256))*256%2C%22.%22%2CINT(A1%2F(256))-INT(A1%2F(256*256))*256%2C%22.0%2F24%22)%3C%2FLI%3E%0A%3CLI%3EPaste%20the%20copied%20formula%20into%20cell%20G1.%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22wfh02.jpg%22%20style%3D%22width%3A%20975px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F181804i6899346D22FA890E%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22wfh02.jpg%22%20alt%3D%22wfh02.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3COL%20start%3D%223%22%3E%0A%3CLI%3ESelect%20column%20G%2C%20click%20the%20%E2%80%9CFill%E2%80%9D%20button%20and%20select%20%E2%80%9CDown.%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22wfh03.jpg%22%20style%3D%22width%3A%20200px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F181831iD23D065F084A23A0%2Fimage-size%2Fsmall%3Fv%3D1.0%26amp%3Bpx%3D200%22%20title%3D%22wfh03.jpg%22%20alt%3D%22wfh03.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3COL%20start%3D%224%22%3E%0A%3CLI%3EIt%20will%20then%20convert%20the%20values%20from%20column%20A%20to%20IP%20address%20ranges.%20A%20small%20sample%20is%20shown%20below.%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22wfh04.jpg%22%20style%3D%22width%3A%20200px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F181802i7A093B3754384086%2Fimage-size%2Fsmall%3Fv%3D1.0%26amp%3Bpx%3D200%22%20title%3D%22wfh04.jpg%22%20alt%3D%22wfh04.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20style%3D%22font-family%3A%20inherit%3B%22%3ESave%20the%20file%20(IPLOCATION.CSV)%20to%20keep%20maintain%20the%20converted%20IP%20ranges.%20These%20columns%20are%20the%20foundation%20that%20will%20provide%20the%20best%20effort%20relative%20location%20without%20building%20data%20being%20uploaded.%20At%20this%20time%2C%20everything%20is%20simplified%20with%20a%2024%20bit%20mask.%20We%20will%20continue%20to%20improve%20and%20expand%20the%20ranges%20out.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EThe%20next%20step%20is%20to%20associate%20the%20IP%20database%20with%20the%20Power%20BI%20Teams%20Utilization%20Report.%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId-1106354643%22%20id%3D%22toc-hId-1106354643%22%20id%3D%22toc-hId-1106354643%22%20id%3D%22toc-hId-1106354643%22%20id%3D%22toc-hId-1106354643%22%20id%3D%22toc-hId-1106354643%22%20id%3D%22toc-hId-1106354643%22%20id%3D%22toc-hId-1106354643%22%20id%3D%22toc-hId-1106354643%22%20id%3D%22toc-hId-1106354643%22%20id%3D%22toc-hId-1106354643%22%20id%3D%22toc-hId-1106354643%22%20id%3D%22toc-hId-1106354643%22%20id%3D%22toc-hId-1106354643%22%20id%3D%22toc-hId-1106354643%22%3E%3CFONT%20color%3D%22%23993366%22%3EImport%20IP%20Data%20In%20PowerBI%3C%2FFONT%3E%3C%2FH2%3E%0A%3CP%3EOpen%20the%20Teams%20Utilization%20Power%20BI%20report.%3C%2FP%3E%0A%3COL%3E%0A%3CLI%3EClick%20on%20the%20%E2%80%9CGet%20Data%E2%80%9D%20option%20from%20the%20Home%20ribbon%20and%20select%20Excel.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLI%3E%0A%3CLI%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22wfh05.jpg%22%20style%3D%22width%3A%20200px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F181814iB1963FACDCB61E64%2Fimage-size%2Fsmall%3Fv%3D1.0%26amp%3Bpx%3D200%22%20title%3D%22wfh05.jpg%22%20alt%3D%22wfh05.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FLI%3E%0A%3CLI%3EBrowse%20to%20the%20directory%20of%20the%20IPLocations.csv%20file.%20Verify%20%E2%80%9CAll%20Files%E2%80%9D%20is%20selected%2C%20then%20click%20Open.%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22wfh06.jpg%22%20style%3D%22width%3A%20200px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F181815iD1490CF51F011152%2Fimage-size%2Fsmall%3Fv%3D1.0%26amp%3Bpx%3D200%22%20title%3D%22wfh06.jpg%22%20alt%3D%22wfh06.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CP%20class%3D%22lia-indent-padding-left-30px%22%3EWhen%20importing%20the%20data%2C%20the%20option%20to%20%E2%80%9CTransform%20Data%E2%80%9D%20is%20presented.%20Select%20this%20option.%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22wfh07.jpg%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F183164i360495F0E8D41570%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22wfh07.jpg%22%20alt%3D%22wfh07.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3COL%20start%3D%223%22%3E%0A%3CLI%3EColumns%20can%20be%20removed%20or%20renamed%20as%20needed.%20Columns%203%20through%208%20will%20be%20renamed%20in%20this%20example.%20%26nbsp%3B%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22wfh08.jpg%22%20style%3D%22width%3A%20200px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F181817i068805E5B2E27366%2Fimage-size%2Fsmall%3Fv%3D1.0%26amp%3Bpx%3D200%22%20title%3D%22wfh08.jpg%22%20alt%3D%22wfh08.jpg%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22wfh09.jpg%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F181818iE3D58973946B260E%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22wfh09.jpg%22%20alt%3D%22wfh09.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FLI%3E%0A%3CLI%3EClick%20%E2%80%9CClose%20%26amp%3B%20Apply%E2%80%9D%20on%20the%20far%20left%20of%20the%20Home%20ribbon.%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22wfh10.jpg%22%20style%3D%22width%3A%20200px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F181819iD67D41FF5EB7D09A%2Fimage-size%2Fsmall%3Fv%3D1.0%26amp%3Bpx%3D200%22%20title%3D%22wfh10.jpg%22%20alt%3D%22wfh10.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EAccept%20the%20security%20warning%20prompt%20when%20it%E2%80%99s%20presented.%3C%2FP%3E%0A%3CP%3EThe%20IP%20location%20data%20can%20now%20be%20found%20under%20the%20data%20model%20in%20the%20Teams%20Utilization%20Power%20BI.%3C%2FP%3E%0A%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22wfh12.jpg%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F181820iCEA7B0C1600A5798%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22wfh12.jpg%22%20alt%3D%22wfh12.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId--701099820%22%20id%3D%22toc-hId--701099820%22%20id%3D%22toc-hId--701099820%22%20id%3D%22toc-hId--701099820%22%20id%3D%22toc-hId--701099820%22%20id%3D%22toc-hId--701099820%22%20id%3D%22toc-hId--701099820%22%20id%3D%22toc-hId--701099820%22%20id%3D%22toc-hId--701099820%22%20id%3D%22toc-hId--701099820%22%20id%3D%22toc-hId--701099820%22%20id%3D%22toc-hId--701099820%22%20id%3D%22toc-hId--701099820%22%20id%3D%22toc-hId--701099820%22%20id%3D%22toc-hId--701099820%22%3E%3CFONT%20color%3D%22%23993366%22%3EJoining%20Datasets%20in%20PowerBI%3C%2FFONT%3E%3C%2FH2%3E%0A%3CP%3EThe%20final%20step%20in%20this%20walkthrough%20is%20to%20join%20the%20Direct%20Query%20and%20imported%20IPLocation%20datasets.%20In%20the%20modeling%20tab%2C%20select%20%E2%80%9CManage%20Relationships%E2%80%9D%20found%20in%20the%20middle%20of%20the%20Home%20ribbon.%3C%2FP%3E%0A%3COL%3E%0A%3CLI%3ECreate%20a%20new%20many%20to%20one%20relationship%20between%20the%20CQD%20Data%20Source%20and%20your%20newly%20created%20IP%20table%20using%20the%20Second%20Reflexive%20Local%20IP%20in%20the%20Direct%20Query%20Table%20and%20the%20ReflexiveEnd%20column%20from%20IPLocations.%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22wfh13.jpg%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F181823i7FC4125569A2C4F2%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22wfh13.jpg%22%20alt%3D%22wfh13.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3COL%3E%0A%3CLI%3ESelect%20%E2%80%9CNew%E2%80%9D.%3C%2FLI%3E%0A%3CLI%3ESelect%20the%20columns%20that%20will%20have%20the%20relationship%20created%2C%20and%20make%20it%20%E2%80%9CMany%20to%20One(*%3A1)%E2%80%9D.%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22wfh15.jpg%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F181824iF484D8C273104320%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22wfh15.jpg%22%20alt%3D%22wfh15.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FLI%3E%0A%3CLI%3EClick%20Ok%20when%20complete.%3C%2FLI%3E%0A%3CLI%3EClose%20the%20relationship%20management%20window.%26nbsp%3B%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22wfh16.jpg%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F181832iE591DEE8931C246B%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22wfh16.jpg%22%20alt%3D%22wfh16.jpg%22%20%2F%3E%3C%2FSPAN%3E%3CP%3EThe%20datasets%20are%20now%20joined%20and%20should%20be%20ready%20for%20use%20in%20Power%20BI%20visualizations.%3C%2FP%3E%0A%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CH2%20id%3D%22toc-hId-1786413013%22%20id%3D%22toc-hId-1786413013%22%20id%3D%22toc-hId-1786413013%22%20id%3D%22toc-hId-1786413013%22%20id%3D%22toc-hId-1786413013%22%20id%3D%22toc-hId-1786413013%22%20id%3D%22toc-hId-1786413013%22%20id%3D%22toc-hId-1786413013%22%20id%3D%22toc-hId-1786413013%22%20id%3D%22toc-hId-1786413013%22%20id%3D%22toc-hId-1786413013%22%20id%3D%22toc-hId-1786413013%22%20id%3D%22toc-hId-1786413013%22%20id%3D%22toc-hId-1786413013%22%20id%3D%22toc-hId-1786413013%22%3E%3CFONT%20color%3D%22%23993366%22%3E%3CSPAN%3ECreate%20Visualizations%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FH2%3E%0A%3CP%3EIf%20the%20column%20names%20remain%20the%20same%2C%20the%20last%20tab%20of%20the%20Teams%20Utilization%20Power%20BI%20can%20be%20used%20as%20shown%20below.%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20the%20column%20names%20changed%20during%20the%20database%20prep%20and%20import%20process%2C%20then%20the%20values%20will%20need%20to%20be%20changed%20for%20the%20visuals%20and%20slicers%20on%20the%20tab.%20If%20preferred%2C%20net%20new%20visualizations%20may%20be%20created%20as%20well.%3C%2FP%3E%0A%3CP%3EBy%20default%2C%20all%20information%20for%20all%20networks%20is%20displayed.%20Apply%20a%20page%20filter%20on%20second%20subnet%20for%20common%20home%20networks%20to%20visualize%20users%20at%20home.%3C%2FP%3E%0A%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22wfh17.jpg%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F183166iB7E601E56C135107%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22wfh17.jpg%22%20alt%3D%22wfh17.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1277627%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECall%20Quality%20Dashboard%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ECQD%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1293809%22%20slang%3D%22en-US%22%3ERe%3A%20Mapping%20External%20Users%E2%80%99%20Locations%20with%20CQD%20Data%20and%20PowerBI%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1293809%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F418591%22%20target%3D%22_blank%22%3E%40andymarsh%3C%2FA%3E%26nbsp%3B-%20thanks%20for%20the%20feedback.%20the%20screenshot%20was%20outdated%2C%20the%20doc%20has%20been%20updated%20now.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1295336%22%20slang%3D%22en-US%22%3ERe%3A%20Mapping%20External%20Users%E2%80%99%20Locations%20with%20CQD%20Data%20and%20PowerBI%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1295336%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F609920%22%20target%3D%22_blank%22%3E%40MartySchlacter%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20was%20struggling%20with%20the%20same%20issue.%20As%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F50347%22%20target%3D%22_blank%22%3E%40Siunie%20Sutjahjo%3C%2FA%3E%26nbsp%3Bstates%20don't%20open%20the%20CSV%20just%20import%20it%20into%20PBI%20skipping%20the%20IP%20database%20preparation%20step.%20However%20I%20could%20not%20get%20the%20expression%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F50347%22%20target%3D%22_blank%22%3E%40Siunie%20Sutjahjo%3C%2FA%3E%26nbsp%3Bposted%20to%20work%20when%20trying%20to%20transform%20the%20data%20to%20get%20the%20IP%20from%20the%20StartDecimal%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20get%20it%20working%20I%20used%20the%20Data%20view%20(not%20transform%20data)%26nbsp%3B%20then%20hit%20New%20Column%20and%20use%20the%20DAX%20expression%20below.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3ERefelxive%20%3D%20INT('IP2LOCATION-LITE-DB3'%5BStartDecimal%5D%2F(256*256*256))%26amp%3B%22.%22%26amp%3B(INT('IP2LOCATION-LITE-DB3'%5BStartDecimal%5D%2F(256*256))-INT('IP2LOCATION-LITE-DB3'%5BStartDecimal%5D%2F(256*256*256))*256%26amp%3B%22.%22)%26amp%3B(INT('IP2LOCATION-LITE-DB3'%5BStartDecimal%5D%2F(256))-INT('IP2LOCATION-LITE-DB3'%5BStartDecimal%5D%2F(256*256))*256%26amp%3B%22.0%2F24%22)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3EHope%20this%20helps%2C%20I've%20been%20struggling%20with%20it%20all%20morning%20but%20I'm%20new%20to%20Power%20BI%20so%20I'm%20sure%20there%20are%20better%20solutions%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1361568%22%20slang%3D%22en-US%22%3ERe%3A%20Mapping%20External%20Users%E2%80%99%20Locations%20with%20CQD%20Data%20and%20PowerBI%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1361568%22%20slang%3D%22en-US%22%3E%3CP%3EOne%20thing%20to%20call%20out%20when%20creating%20the%20table%20relationship%20between%20the%20CQD%20data%20table%20(Query1)%20and%20the%20IPLOCATION%20data%20table%20is%20that%20column%20G%20has%20to%20contain%20unique%20values%20in%20order%20to%20create%20the%20Many%20to%201%20relationship%20from%20Query1%20to%20IPLOCATION.%20Otherwise%20it'll%20only%20allow%201%20to%20Many%20which%20will%20not%20work.%3CBR%20%2F%3EYou%20can%20open%20the%20IPLOCATION.CSV%20in%20Excel%20and%20select%20the%20columns%20with%20data%20and%20under%20Data%20on%20the%20Ribbon%20bar%20select%20Remove%20Duplicates.%26nbsp%3B%20Then%20Unselect%20All%20and%20just%20select%20Column%20G%20and%20then%20save%20and%20refresh%20in%20Power%20BI.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1362502%22%20slang%3D%22en-US%22%3ERe%3A%20Mapping%20External%20Users%E2%80%99%20Locations%20with%20CQD%20Data%20and%20PowerBI%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1362502%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20downloaded%20the%20latest%20Teams%20utilisation%20PBIX%20and%20the%20last%20tab%20doesn't%20seem%20to%20have%20the%20picture%20you%20show.%20do%20you%20know%20what%20dashboard%20this%20is%20a%20part%20of%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1506249%22%20slang%3D%22en-US%22%3ERe%3A%20Mapping%20External%20Users%E2%80%99%20Locations%20with%20CQD%20Data%20and%20PowerBI%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1506249%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F50347%22%20target%3D%22_blank%22%3E%40Siunie%20Sutjahjo%3C%2FA%3E%2C%3C%2FP%3E%3CP%3EI%20have%20tried%20the%20same%20approach%20and%20trying%20build%20the%20report%20on%20external%20calls%20geographical%20analysis%20across%20the%20globe.%20I%20had%20downloaded%26nbsp%3B%3CSTRONG%3EIP2LOCATION-LITE-DB3.CSV%2C%26nbsp%3BIP2PROXY-LITE-PX3.CSV%20%3C%2FSTRONG%3Eand%26nbsp%3B%3CSTRONG%3EIP2PROXY-LITE-PX4.CSV%3C%2FSTRONG%3E%20files%20and%20appended%20at%20Query%20Editor%20and%20created%20Reflex%20column%20but%20this%20was%20able%20to%20map%20only%202%25%20of%20the%20total%20streams.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1984%22%20target%3D%22_blank%22%3E%40Nirmal%20Sundara%20Raman%3C%2FA%3E%2C%26nbsp%3Bany%20comments%20on%20this%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20build%20this%20report%20from%20couple%20of%20months%20but%20still%20I%20couldn't%20get%20the%20perfect%20solution.%20your%20help%20would%20be%20highly%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%2C%3C%2FP%3E%3CP%3EPraveen%3C%2FP%3E%3C%2FLINGO-BODY%3E
Microsoft

With the global crisis of COVID-19 and the impact on the global workforce, the ability to see utilization metrics from an external standpoint (for example: a user working from home) has become increasingly important for Teams IT Admins.

To see network information broken down between internal/external networks and to take full advantage of the Advanced Call Quality Dashboard (CQD) data, Microsoft highly recommends that a Building Data File be created and uploaded. Out of the many reasons for uploading the data file, one key piece of information relied upon is this delineation between internal networks (deployed and managed by organization IT admin) and external networks (managed by end-users or a private entity, such as: coffee shops).  Knowing the call volume, call quality & reliability on the organization network (wired or WiFi) is important, as this is within the organizations sphere of control. Uploading a building data will expedite the organization IT admin in locating where problems occur in a specific region/site/location/building/floors of interest during call quality and reliability troubleshooting. External networks are managed by home users or private entity (such as a coffee shop owner). There is little action to occur on this front, other than providing recommended connectivity practices to the end users.

In keeping with the spirit of “Empowering every person and organization on the planet to achieve more” Microsoft has undertaken an effort to help those organizations currently lacking a building data file. By leveraging a third-party ISP/IP location database in conjunction with the reflexive IP address associated with a user, an approximate location can be determined to differentiate internal and external connectivity. Do keep in mind that the published/downloadable Microsoft Call Quality Power BI connector and associated Power BI templates work best when building data is provided.

To visualize the locations of home users in CQD Power BI, follow the steps below. This guidance will help identify the locations and ISP providers via 3rd party Geo IP databases.  This example is provided for instructional purposes.  It is up to you to follow any laws or regulations that might apply to mapping locations of your users based on their IP addresses. 

wfh17.jpg

Download IP addresses from an IP address database.  

If your organization already has a subscription to an IP Geolocation Database, this may be used if it has the required information in it.

If not, search for an IP Geolocation Database and download it. This may require signing up for an account with the third-party providing the database.

One such provider is https://lite.ip2location.com. But you could download similar data from any other database for other reasons.  Note that the accuracy of your results will depend on the precision of the IP-to-location data set.

 

After extracting the contents from the ZIP file, open the CSV file to see contents like the screenshot below.

wfh01.jpg

Note: Query times may be optimized by filtering out extraneous data from the CSV. For example, if your organization only operates out of the United States, all other countries can be excluded.

IP Database Preparation

Columns A and B in the above example are the IP ranges (start and end) represented in a decimal format. These do not look like traditional IP addresses and will need to be converted to a more recognizable format.

The formula to use in this instance is as follows:

= CONCATENATE(INT(A1/(256*256*256)),".",INT(A1/(256*256))-INT(A1/(256*256*256))*256,".",INT(A1/(256))-INT(A1/(256*256))*256,".0/24")

  1. Copy formula from the equal sign to the end of the line.
    = CONCATENATE(INT(A1/(256*256*256)),".",INT(A1/(256*256))-INT(A1/(256*256*256))*256,".",INT(A1/(256))-INT(A1/(256*256))*256,".0/24")
  2. Paste the copied formula into cell G1.

wfh02.jpg

  1. Select column G, click the “Fill” button and select “Down.

wfh03.jpg

  1. It will then convert the values from column A to IP address ranges. A small sample is shown below.

wfh04.jpg

Save the file (IPLOCATION.CSV) to keep maintain the converted IP ranges. These columns are the foundation that will provide the best effort relative location without building data being uploaded. At this time, everything is simplified with a 24 bit mask. We will continue to improve and expand the ranges out.

The next step is to associate the IP database with the Power BI Teams Utilization Report.

Import IP Data In PowerBI

Open the Teams Utilization Power BI report.

  1. Click on the “Get Data” option from the Home ribbon and select Excel.

  2. wfh05.jpg
  3. Browse to the directory of the IPLocations.csv file. Verify “All Files” is selected, then click Open.
    wfh06.jpg

When importing the data, the option to “Transform Data” is presented. Select this option.
wfh07.jpg

  1. Columns can be removed or renamed as needed. Columns 3 through 8 will be renamed in this example.  
    wfh08.jpgwfh09.jpg
  2. Click “Close & Apply” on the far left of the Home ribbon.

wfh10.jpg

Accept the security warning prompt when it’s presented.

The IP location data can now be found under the data model in the Teams Utilization Power BI.

wfh12.jpg

Joining Datasets in PowerBI

The final step in this walkthrough is to join the Direct Query and imported IPLocation datasets. In the modeling tab, select “Manage Relationships” found in the middle of the Home ribbon.

  1. Create a new many to one relationship between the CQD Data Source and your newly created IP table using the Second Reflexive Local IP in the Direct Query Table and the ReflexiveEnd column from IPLocations.

wfh13.jpg

  1. Select “New”.
  2. Select the columns that will have the relationship created, and make it “Many to One(*:1)”.
    wfh15.jpg
  3. Click Ok when complete.
  4. Close the relationship management window. 
    wfh16.jpg

    The datasets are now joined and should be ready for use in Power BI visualizations.

Create Visualizations

If the column names remain the same, the last tab of the Teams Utilization Power BI can be used as shown below. 

If the column names changed during the database prep and import process, then the values will need to be changed for the visuals and slicers on the tab. If preferred, net new visualizations may be created as well.

By default, all information for all networks is displayed. Apply a page filter on second subnet for common home networks to visualize users at home.

wfh17.jpg

 

9 Comments
New Contributor

@Siunie Sutjahjo Thanks for documenting this. This will be very helpful to monitor and take call quality to next level.

Occasional Visitor

Is there a way to ensure Excel opens the entire CSV file...on Win10 & macOS, it stops loading content past 1,048,576 rows...stops at, roughly, the Netherlands.  Should I break the CSV file into chunks and process them separately?

Microsoft

@MartySchlacter - you can try to open it directly in PBI using a new column - here is for your reference:

Reflexive1 = CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE(INT('IP2LOCATION-LITE-DB3'[StartDecimal]/(256*256*256)),"."),INT('IP2LOCATION-LITE-DB3'[StartDecimal]/(256*256))-INT('IP2LOCATION-LITE-DB3'[StartDecimal]/(256*256*256))*256),"."),INT('IP2LOCATION-LITE-DB3'[StartDecimal]/(256))-INT('IP2LOCATION-LITE-DB3'[StartDecimal]/(256*256))*256),".0/24")

Thanks to @Nirmal Sundara Raman who has tried it directly in PBI
Senior Member

On step 4 of the sheet, I see column G done but on step 3 of importing into Power BI there is two columns around start ip and end ip with the last octet missing (column 7 and 8)

 

I don't seem to be able to get to that place so suggests some manual work was done?

 

can anyone advise?

Microsoft

@andymarsh - thanks for the feedback. the screenshot was outdated, the doc has been updated now.

Regular Visitor

@MartySchlacter

 

I was struggling with the same issue. As @Siunie Sutjahjo states don't open the CSV just import it into PBI skipping the IP database preparation step. However I could not get the expression @Siunie Sutjahjo posted to work when trying to transform the data to get the IP from the StartDecimal column.

 

To get it working I used the Data view (not transform data)  then hit New Column and use the DAX expression below.

 

Refelxive = INT('IP2LOCATION-LITE-DB3'[StartDecimal]/(256*256*256))&"."&(INT('IP2LOCATION-LITE-DB3'[StartDecimal]/(256*256))-INT('IP2LOCATION-LITE-DB3'[StartDecimal]/(256*256*256))*256&".")&(INT('IP2LOCATION-LITE-DB3'[StartDecimal]/(256))-INT('IP2LOCATION-LITE-DB3'[StartDecimal]/(256*256))*256&".0/24")
 
Hope this helps, I've been struggling with it all morning but I'm new to Power BI so I'm sure there are better solutions
Microsoft

One thing to call out when creating the table relationship between the CQD data table (Query1) and the IPLOCATION data table is that column G has to contain unique values in order to create the Many to 1 relationship from Query1 to IPLOCATION. Otherwise it'll only allow 1 to Many which will not work.
You can open the IPLOCATION.CSV in Excel and select the columns with data and under Data on the Ribbon bar select Remove Duplicates.  Then Unselect All and just select Column G and then save and refresh in Power BI.

Senior Member

I have downloaded the latest Teams utilisation PBIX and the last tab doesn't seem to have the picture you show. do you know what dashboard this is a part of?

Regular Visitor

Hi @Siunie Sutjahjo,

I have tried the same approach and trying build the report on external calls geographical analysis across the globe. I had downloaded IP2LOCATION-LITE-DB3.CSV, IP2PROXY-LITE-PX3.CSV and IP2PROXY-LITE-PX4.CSV files and appended at Query Editor and created Reflex column but this was able to map only 2% of the total streams.

@Nirmal Sundara Raman, any comments on this

I am trying to build this report from couple of months but still I couldn't get the perfect solution. your help would be highly appreciated.

 

Regards,

Praveen