Table/Measure to retrieve certain information

[Anonymous]

Hi everyone,

======
First post 🙂
======
Currently, I am working on a project which will help me identify certain areas that are not logging in or engaging with the portal they are provided.
I'm extracting the data from Dynamics ( CRM ) and trying to implement the raw data in a filled map on PowerBI.

I have 2 tables currently with the following :
Table no.1: Conference with active login to the portal in the last 31 days by association

- This table provides the following details :

-Columns: Name, New agresso code, last login to the portal, location, Area, Region code, Country, Region, Country, Country short for Bing

-Shows the user which *confrences have been actively logging in the past 31 days ( only shows last login in date/time format, not multiple figures for a confrence )

*A confrence is a location where all of the members meetup to liasie/work and logon to the portal "inhouse" > we have named the confrence under "Name" in both tables 

Table no.2: Home visitation confrence 

- This table provides the following details :

- Columns: Region, Area, Name, Location, Main activity, New agresso code, President, Home address

- We are focusing on the following columns : Name, Location/region & last login to the portal ( both tables contain Name & region : trying to build a relationship)

-Other columns can be ignored.

Desired outcome:

• Extract the confrences that have not been active within the 31 days.
  • Table no.1 : Contains the active confrence in 31 days
  • Table no.2 : Contains all Confrences in the whole country 

• Highlight/colour code the confrences not interacting( Ones not on the Confrence with active login to the portal in last 31 days by association table) on the map to easily identify the confrences that are not interacting with the portal. ( By region/Area)

I have hit a brick wall and here are some of the things I have tried :

• I have created a "hierarchy" that can drill down from Region > area 
  • My desired hierarchy was Region > area > confrences, but filled map is very limited in functionality and the confrences cannont be mapped accurately ( no long/latitude details or geo-location)

• INTERSECT the records to retrieve the confrences not included on the table : My knowledge of writing queries within PowerBi is very limited but I understand alot of the Syntax 

This is my Logic:

If confrence name[ From home visitation ] has login from this table [ Confrences with active login in last 31 days] : Green

If confrence name[ From home visitation ] has no last login from portal [ Confrences with active login in last 31 days] : Red

Specific confrences would be difficult to highlight, I can colour code based off regions also.

Apologies if this is confusing, this is my first post.

And I hope I can get some insights into where Im going wrong or maybe my logic is wrong.

Thank you in advance , I appreciate any help🙂

[Anonymous · Accepted solution]

UPDATE **

I was able to view all the confrences not logging in/Interacting with the portal:

I used the following code : 

CNLI = EXCEPT(SUMMARIZE('Home Visitation conferences','Home Visitation conferences'[Name]),SUMMARIZE(CWALP31BA,CWALP31BA[Name]))

Now I need to map out the list I have extracted to the filled map for each region.

@v-zhangti 

Here is a link to all the files im using: 

https://drive.google.com/drive/folders/1NaCHBMxz0X79n4GWLSRY3B7i2m44vMIa?usp=sharing

Any other suggestions would be much appreciated.

[v-zhangti]

Hi, @Anonymous 

Can you provide some sample data or simple pbix files? Sensitive information can be removed in advance.

Best Regards,

Community Support Team _Charlotte

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