Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RoundedWheels
Employee
Employee

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
DataZoe
Employee
Employee

@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

 

Respectfully,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

View solution in original post

4 REPLIES 4
v-shex-msft
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.
Anonymous
Not applicable

@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.

DataZoe
Employee
Employee

@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

 

Respectfully,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

amitchandak
Super User
Super User

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.