Usage Metrics - Country of origin by email

[benk3i]

Hi,

I am connected to the "Usage Metrics" dataset for a report, and I am trying to show which country each user comes from. In the dataset, there is a "Users[UserPrincipalName]" that contains the email adress. 

Since I cannot transform the data (supposedly because it is a connected dataset), my idea was to create a measure that says

• When looking at the table Users[UserPrincipalName], if a row contains ".es", then return the value "Spain". 
• Then create a long measure that includes all countries in my company. 
• Then I can show "Views from Spain: X"

I have tried various ways without success. 

Any ideas?

[benk3i]

Hi @Anonymous ,

Sorry if my question is a bit unclear. Let me try to clarify. 

As of now I have not found a solution to my problem. Unfortunately I cannot share the PBIX file as you would not have access to the dataset. It may be that what I want done, cannot be done because it is a connected dataset. But that is what I am trying to find out.

• I am connected to a live dataset, which is the Usage Metrics that you can find in Power BI for every report.
• In that dataset, one of hte fields I am provided with by defaul is the UserPrincipalName, which is the email address of every user that visits the report
• Based on that, I have with your help created a measure that goes (the code editor is not working for some reason, so bear with me)
  • Country/Platform =
    SWITCH(
    RIGHT(
    MAX('Users'[UserPrincipalName]),9),
    "ending.es","Spain",
    "ending.au","Australia")
• That gives me a table that looks like:

  • UserPrincipalName	Country/Platform	ViewsCount
    User1@ending.es	Spain	20
    User2@ending.es	Spain	15
    User3@ending.au	Australia	10
    User4@ending.au	Australia	40

• So far so good. The problem I get is when I want to do the final step, and summarize how many views Spain has, and how many views Australia has. 
  • What I want to see is:

    • Country/Platform	ViewsCount
      Spain	35
      Australia	50

  • However, when I create a table combining "Country/Platform" and "ViewsCount", it only shows 

    • Country/Platform	ViewsCount
      Australia	50

  • So the data is there, but I cannot get it to expand for all the countries

I hope that clarifies both my issue, and my final objective a bit. Once again, perhaps this is not possible with a live connnected dataset. It is just that I am so close, but can't take hte final step.

Best,

Benk3i

[Anonymous]

Hi @benk3i ,

It could not be achieved by using measure, you could add a calculated column for Country instead:

CountryColumn =
VAR _search =
    SEARCH ( "@", 'User'[UserPrincipalName], 1, BLANK () )
VAR _left =
    IF ( _search > 0, LEFT ( 'User'[UserPrincipalName], _search - 1 ), BLANK () )
VAR _a1 =
    IF ( ISERROR ( _left ), BLANK (), _left )
VAR _right =
    IF ( LEN ( _a1 ) > 1, RIGHT ( _left, 2 ), BLANK () )
RETURN
    SWITCH ( _right, "es", "Spain", "cn", "China", "uk", "UK" )

And we don't need to create another measure, just choose “Sum”(for Value field) and "Show items with no data" (for Country field)

Here is the pbix file.

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.

Best Regards,
Eyelyn Qin

[benk3i]

Hi @Anonymous 

Got you. Then that is probably the underlying issue.  Unfortunately, when I connect the dataset to the Usage Metrics dataset, I am unable to create columns (in my understanding, becuse then i would change the underlying dataset).

So I am guessing there really isnt a solution to this situation then?

Best,

benk3i

[Anonymous]

Hi @benk3i ,

Sorry for my late reply. But I'm really a little confused about your real needs...

When you get data from Power BI Dateset ,the connection mode is Live Connection which does not support Power Editor.

Could you tell me if your problem previous has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,
Eyelyn Qin

[Anonymous]

Hi @benk3i ,

Sorry for my misunderstand and sorry for my incomplete consideration,

From my test, this error will be reported when there is a null value, a very short character, do not exist "@"  ,etc

So I improved my data, please try like this:

Measure =
VAR _search =
    SEARCH ( "@", MAX ( 'User'[UserPrincipalName] ), 1, BLANK () )
VAR _left =
    IF (
        _search > 0,
        LEFT ( MAX ( 'User'[UserPrincipalName] ), _search - 1 ),
        BLANK ()
    )
VAR _a1 =
    IF ( ISERROR ( _left ), BLANK (), _left )
VAR _right =
    IF ( LEN ( _a1 ) > 1, RIGHT ( _left, 2 ), BLANK () )
RETURN
    SWITCH ( _right, "es", "Spain", "cn", "China", "uk", "UK" )

Then the output will be right:

Here is my pbix file.

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.

Best Regards,
Eyelyn Qin

[benk3i]

Hi @Anonymous ,

You have been super helpful and I have managed to get pretty far with all of your help. 

I actually ended up going with something closer to your original suggestion, which was:

Country/Platform = 
SWITCH(
        RIGHT(
            MAX('Users'[UserPrincipalName]),2),
            "es","Spain",
            "cn","China",
            "uk","UK",)

The email adresses I have are all based on the domain ending, so not before the "@" symbol, but I assume that your latest suggestion might work even better in case of complications. 

That worked great in the table. However, I am unable to create a table that shows

"Spain: X views"

"China: X views"

"UK: X views".

Similarly to the PBIX file that you attached, if you try to create a table with the measure and values, it just shows "UK: 21" (see the picture below)

What I would like in your example is to show the table as 

Measure	Value
China	1
Spain	2
UK	3
Blank	15

Maybe that is a bit beyond what a measure can do though. Haven't tried something like this before. 

Once again, thanks a lot for all the detailed help!

[Anonymous]

Hi @benk3i ,

It seems like the [UserPrincipalName] is a measure not a column from original data source ,right?

Please change like this:

Measure =
SWITCH (
    RIGHT (
        LEFT (
            [UserPrincipalNameMeasure],
            SEARCH ( "@", [UserPrincipalNameMeasure], 1, BLANK () ) - 1
        ),
        2
    ),
    "es", "Spain",
    "cn", "China",
    "uk", "UK"
)

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.

Best Regards,
Eyelyn Qin

[benk3i]

Hi @Anonymous 

Actually the UserPrincipalName is not actually a measure. The fields panel looks like the picture below. The only measure I have there right now is the one from above. 

 

Best,

Benk3i

[Anonymous]

Hi @benk3i ,

There is a little difference between Column and Measure, please take a look at the following formula:

measure =
SWITCH (
    RIGHT (
        LEFT (
            MAX ( 'User'[UserPrincipalName] ),
            SEARCH ( "@", MAX ( 'User'[UserPrincipalName] ), 1, BLANK () ) - 1
        ),
        2
    ),
    "es", "Spain",
    "cn", "China",
    "uk", "UK"
)

 Best Regards,
Eyelyn Qin

[benk3i]

Hi @Anonymous 

Thanks for the quick reply! 

That made it so that I can find the Column, so that's the good news! 

Unfortunately I end up the following error when I try (seems to refer to the "Left("

This was when I tried to put the UserPrincipal in the same table with the measure. 

I also get no values in the table when I just use the measure. 

Best,

Benk3i

[Anonymous]

Hi @benk3i ,

According to my understand, you just want to know which country each user comes from based on email address, it seems like we do not need Usage Metrics  (It is used to see  who viewed the report, how many times, etc.)

You could use the following formula to creata a Country column:

Country =
SWITCH (
    RIGHT (
        LEFT (
            'User'[UserPrincipalName],
            SEARCH ( "@", 'User'[UserPrincipalName], 1, BLANK () ) - 1
        ),
        2
    ),
    "es", "Spain",
    "cn", "China",
    "uk", "UK"
)

Then you could apply filter or use Q&A in Desktop to filter Country. Also, URL filter is another  effective way when the report is published to service. 

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.

Best Regards,
Eyelyn Qin

[benk3i]

Hi @Anonymous ,

Thank you for your response. This absolutely feel like the right solution. 

However, I am unable to select the right table/field when I am typing in the measure.

Country =
SWITCH (
    RIGHT (
        LEFT (
            'User'[UserPrincipalName],

The measure will not recognize 'Users'[UsersPrincipalName]. It will suggest existing measures. In the usage metrics there are two measures created with the calculator icon, and it immediately suggests those two, but not the table I am looking for - as seen below

Even if I type it in, the code doesnt work and I get an error (red underline), so it doesnt find the table/field.

 Here are the fields

Maybe my problem is extremely basic, but I am confused 🙂