cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Microsoft
Microsoft

How can I add a new table to a live connected database?

I have a database that includes a field with the "customer name". I need to group these customers, and for that I would usually create a new table with two colums (one with the "customer name" and another with the "group name"), and then relate both through the "customer name" field. 

 

While this works when the database is imported to the PowerBI file, it doesn't when I just establish a live connection to the database (Get Data -> Analysis Services -> SQL Server Analysis Services database -> Connect live). Basically, PowerBI disables the option to create a new table.

 

I heard that I might be able to do this using "Power Apps", but I have never used them and I am lost. Can anyone provide some help on where I can start looking at or let me know whether there is a different way to get done what I need?

 

Thanks in advance.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User I
Super User I

@RoundedWheels Unfortunately, you have to add that table in at AAS level. Power Apps gives you the ability to write to a table you have direct query attached to your model, not sure it can help out with this scenario. 

 

What you can add is measures, and you may be able to get close to what you are trying to do. that is assuming your Customer Name list is overly long.

 

1. I assume you have created this grouping in Excel or can open it there.

2. You can create Excel formulas to put a table of values into one quoted and comma delimited string like so:

DataZoe_0-1602035210022.png

3. Use these to create a measure for each group:

 

Group 1 Customers =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Customer Name] ),
    FILTER (
        'Table',
        'Table'[Customer Name]
            IN {
            "Krispin Klicher",
            "Francisca Adriano",
            "Elliott Windybank",
            "Abbye Manston",
            "Kerry Muzzillo",
            "Jeane Muddle",
            "Fernande Moehler",
            "Rice Pavluk",
            "Horatius Hercules",
            "Susanetta Gallie",
            "Bent Wasmer",
            "Dosi Cosford",
            "Joel Betje",
            "Libby Bernot",
        }
    )
)

 

Group 2 Customers =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[id] ),
    FILTER (
        'Table',
        'Table'[Customer Name]
            IN {
            "Gun Samett",
            "Brockie Jacklin",
            "Marve McKeowon",
            "Francisco Tilio",
            "Ludwig Senett",
            "Welch Lampel",
            "Lory Jozsika",
            "Alfredo Philipsohn",
            "Jim Aulton",
            "Sander Banister",
            "Kearney O'Criane",
            "Kristoforo Maxwale",
            "Janene Elmore",
            "Alika Pester",
            "Timotheus Dicey",
            "Liesa Pavolini",
            "Pat Hounsome",
            "Nial Pilkington",
            "Ginelle Worrill",
            "Fredericka Ludwell",
            "Cinda Elen"
        }
    )
)

 

Group 3 Customers =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[id] ),
    FILTER (
        'Table',
        'Table'[Customer Name]
            IN {
            "Obediah Musgrave",
            "Dorothy Widd",
            "Erskine Caudray",
            "Nathalie Fumagallo",
            "Karim Sheahan",
            "Duff Haddleston",
            "Elwin Cage",
            "Feodora McKag",
            "Krispin Pinar",
            "Britta Tabour",
            "Alys Dohmer",
            "Nefen Betje"
        }
    )
)

 

Customer Group =
SWITCH (
    FALSE (),
    ISBLANK ( [Group 1 Customers] )"Group 1",
    ISBLANK ( [Group 2 Customers] )"Group 2",
    ISBLANK ( [Group 3 Customers] )"Group 3",
    "No Group"
)

3. You can use 3 counts in visuals instead of a legend, such as a pie chart. And you can use the Customer Group in tables and you can filter individual visuals with it using the filter pane. You can't of course, use it as a legend, axis, or slicer. 

 

DataZoe_0-1602035641526.png

 

View solution in original post

4 REPLIES 4
Highlighted
Super User IV
Super User IV

@RoundedWheels , Not very Clear. But you can add only Only One Live connections as of now.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Super User I
Super User I

@RoundedWheels Unfortunately, you have to add that table in at AAS level. Power Apps gives you the ability to write to a table you have direct query attached to your model, not sure it can help out with this scenario. 

 

What you can add is measures, and you may be able to get close to what you are trying to do. that is assuming your Customer Name list is overly long.

 

1. I assume you have created this grouping in Excel or can open it there.

2. You can create Excel formulas to put a table of values into one quoted and comma delimited string like so:

DataZoe_0-1602035210022.png

3. Use these to create a measure for each group:

 

Group 1 Customers =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Customer Name] ),
    FILTER (
        'Table',
        'Table'[Customer Name]
            IN {
            "Krispin Klicher",
            "Francisca Adriano",
            "Elliott Windybank",
            "Abbye Manston",
            "Kerry Muzzillo",
            "Jeane Muddle",
            "Fernande Moehler",
            "Rice Pavluk",
            "Horatius Hercules",
            "Susanetta Gallie",
            "Bent Wasmer",
            "Dosi Cosford",
            "Joel Betje",
            "Libby Bernot",
        }
    )
)

 

Group 2 Customers =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[id] ),
    FILTER (
        'Table',
        'Table'[Customer Name]
            IN {
            "Gun Samett",
            "Brockie Jacklin",
            "Marve McKeowon",
            "Francisco Tilio",
            "Ludwig Senett",
            "Welch Lampel",
            "Lory Jozsika",
            "Alfredo Philipsohn",
            "Jim Aulton",
            "Sander Banister",
            "Kearney O'Criane",
            "Kristoforo Maxwale",
            "Janene Elmore",
            "Alika Pester",
            "Timotheus Dicey",
            "Liesa Pavolini",
            "Pat Hounsome",
            "Nial Pilkington",
            "Ginelle Worrill",
            "Fredericka Ludwell",
            "Cinda Elen"
        }
    )
)

 

Group 3 Customers =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[id] ),
    FILTER (
        'Table',
        'Table'[Customer Name]
            IN {
            "Obediah Musgrave",
            "Dorothy Widd",
            "Erskine Caudray",
            "Nathalie Fumagallo",
            "Karim Sheahan",
            "Duff Haddleston",
            "Elwin Cage",
            "Feodora McKag",
            "Krispin Pinar",
            "Britta Tabour",
            "Alys Dohmer",
            "Nefen Betje"
        }
    )
)

 

Customer Group =
SWITCH (
    FALSE (),
    ISBLANK ( [Group 1 Customers] )"Group 1",
    ISBLANK ( [Group 2 Customers] )"Group 2",
    ISBLANK ( [Group 3 Customers] )"Group 3",
    "No Group"
)

3. You can use 3 counts in visuals instead of a legend, such as a pie chart. And you can use the Customer Group in tables and you can filter individual visuals with it using the filter pane. You can't of course, use it as a legend, axis, or slicer. 

 

DataZoe_0-1602035641526.png

 

View solution in original post

Highlighted
Community Support
Community Support

Hi @RoundedWheels,

Nope, it is impossible to do changes to add a table when you work on the connection live mode. (most of the edit features has been disabled in this mode, you can only do changes on measures) You need to do these operations on your database side instead.
Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Highlighted

@v-shex-msft , @amitchandak ,

 

I am using a shared dataset to create a report which has a table visual and few filters on it. 

As it shows as live connected to Dataset, there are most of the restrictions with the options available in the tool.

My Scenario is On a table visual I need to filter data based customer id. So my Customer table has thousands of records out of which i need to filter data for few hundreads customer id. Its difficult to pull the column on the page level filter and  unselect those customer id's from the list. Is there any other way to do so.

I created a measure as below  but I cant place it on Page level filter so added it to Visual level filters. Filteres with Cust id is not 0.

Customer ID Not In =
VAR CustomerID = MAX(CUSTOMER[CUSTOMER_ID])
VAR Customer = {"95611","5621","5395","5396","5636","5410","0543","6180","66487","9749","0339","17","99538","99806","3826","3924","84265","86380","94089","94125","94190","95822","43479","43480","43579","43580" }
RETURN
IF(NOT(CustomerID in Customer),1,0)

 

When i Place this measure on the visual filter I see the Resource exceed error. Any better way of writing this measure or any other possible way to filter data.

Any help on this is highly appreciated.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors