sql

51 Topics

Dax Studio - Python Connection
Hello All, There is a plan to move data source from one database to another for which it would be essential for us to understand the dashboards within a workspace. I have explored few options in Dax Studio which has capability to return Measures, Columns, Catalogs (Dashboards) and other details of a specific workspace. However if I need to get a detailed inforamtion across all the Power BI Dashboards in a workspace, the commands below would not allow me to. SELECT [CATALOG_NAME] FROM $SYSTEM.DBSCHEMA_CATALOGS SELECT [CATALOG_NAME] FROM $SYSTEM.DBSCHEMA_CATALOGS, SELECT [ID],[Name] FROM $SYSTEM.TMSCHEMA_TABLES WHERE NOT [IsHidden] SELECT * FROM $SYSTEM.TMSCHEMA_COLUMNS WHERE NOT [IsHidden] SELECT [ID],[TableID],[Name],[QueryDefinition] FROM $SYSTEM.TMSCHEMA_PARTITIONS SELECT [ID],[TableID],[Name],[Expression] FROM $SYSTEM.TMSCHEMA_MEASURES WHERE NOT [IsHidden] SELECT * FROM $SYSTEM.TMSCHEMA_RELATIONSHIPS Requesting your guidance if there is a way I can loop through all the commands above and get the data into Excel format or into SQL or connect to Power BI directly so that I can analyze it further. Regards Mithun T
mithunt
10 months ago Place DAX Commands and Tips
1.1KViews
0likes
5Comments
Error while connecting PostGres to Power BI
I'm trying to connect my Power BI Desktop to Post Gres SQL. I get the following error when I tried to connect. Details: "Loading assembly file 'System.Runtime.CompilerServices.Unsafe, Version=4.0.4.1, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' failed for data source type 'PostgreSQL'" With the same credentials, im able to connect to the MY SQL workbench
Solved
Gandhar09
2 years ago Place Report Server
4.1KViews
0likes
2Comments
RELATED do not work with SQL direct query
Hi together, I have a few databases on sql servers which a get in powerbi via direct query. I created relationships between the datasources and not want to add new columns using the RELATED command. I always get an error that there is no realtionship or the column which i would to take is not availible. But both failures do not exist. The column is there and i choose it from the suggestions which powerbi makes when i search for it and the relationship is also there. I must go via direct query and not import cause the files are too big. It it possible that RELATED do not work with SQL Servers as sources via direct query? Thanks a lot for your help.
steffens12
2 years ago Place Desktop
519Views
0likes
2Comments
Offline SQL Connection in automatic data refresh but single refresh is possible
Hi Community, I am using a mysql Connection with the Connector Gateway and daily automatically refresh of the data. Yesterday the database hardware was down and our admin configured a new server. Every setting is same then before. I am able to refresh the data manually in the semantic modell: But when I try to reactivate the automatic refresh I get no connection to the sql server. This is strange when it works manually but not autmatically? I have no idea what happened and how I can solve it .. Does anybody has an idea? Thank you Nicole Fehler beim Aktualisieren der Datenquellen-Anmeldeinformationen: An error happened while reading data from the provider: 'One or more errors occurred.'Details ausblenden Aktivitäts-ID: 0dc3065d-0b46-43ee-ad1b-7b492a153e26 Anforderungs-ID: c1d65740-0ca5-3c3d-1a65-026b9e066a5b Statuscode: 400 Zeit: Mon May 27 2024 15:57:46 GMT+0200 (Mitteleuropäische Sommerzeit) Dienstversion: 13.0.23358.54 Clientversion: 2405.3.19359-train Cluster-URI: https://wabi-west-europe-d-primary-redirect.analysis.windows.net/ Fehlercode: UnknownError Verbindungsfehler: Unbekannter Fehler Cluster-URI: https://api.powerbi.com Sitzungs-ID: eceeb10e-7cf4-4b30-8945-67f5b56cda76 Anforderungs-ID: 6a6fd08e-7d5b-495d-8575-4413e0a568fe Zeit: Mon May 27 2024 15:47:37 GMT+0200 (Mitteleuropäische Sommerzeit) : undefined: An exception encountered while accessing the target data source
nludwig
2 years ago Place Service
790Views
0likes
2Comments
Convert SQL To DAX
Hi, I am very new to DAX and have to convert some SSRS into paginated reports using powerbi data source as the model has already been published. I am stuggling converting this stored procedure into DAX, any help would be very much appreciated. The parameters in this stored procedure are multi-valued ones: SELECT DISTINCT table.StaffID, Team, FirstName, LastName, CourseName, DueOnDate FROM [database].[dbo].[table] INNER JOIN ( SELECT StaffID, SUM(TrainingTracked) TrainingTracked, SUM(Denominator) - SUM(Numerator) DemNumDiff, SUM(TrackedDenominator) - SUM(TrackedNumerator) TrackDemNumDiff, CASE WHEN SUM(TrainingTracked) = 16 and SUM(TrackedDenominator) - SUM(TrackedNumerator) > 0 THEN 1 WHEN SUM(TrainingTracked) <> 16 and SUM(Denominator) - SUM(Numerator) > 0 THEN 1 ELSE 0 END NotComplaint FROM [database].[dbo].[table] WHERE [Compliant] = 0 AND Team IN (@Team) AND Course IN (@Course) GROUP BY StaffID ) A ON table.StaffID = a.StaffID WHERE A.NotComplaint = 1 Thank you
Solved
bpdax
2 years ago Place DAX Commands and Tips
2.2KViews
0likes
3Comments
Datamart SQL and SYSDATE
Hi I am looking at developing a datamart and have pulled in tables from Oracle, I want to write a SQL statement for a table using xx_end_date is null or xx_end_date > sysdate This is not working on the datamart SQL. Does anyone have a solution to this? Thanks
Solved
Anonymous
2 years ago Place Report Server
909Views
0likes
2Comments
Remove Duplicate Values using CONCATENATEX and Direct Query
I am having trouble getting values from a Direct Query to appear correctly in a table using the CONCATENATEX fxn. I have tried multiple fxn setups and all present varying issues. Each row corresponds to a specific ID and each Finding (bmpObs) only appears once in the SQL dataset for each ID. The SQL dataset looks similar to this: ID#1 - value1 ID#1 - NULL ID#1 - value2 ID#1 - value3 ID#1 - NULL ID#2 - value1 ID#2 - NULL ID#2 - value4 ID#2 - value6 ID#2 - NULL ID#3 - value2 ID#3 - NULL ID#3 - value4 ID#3 - value5 ID#3 - NULL I'm trying to get a result that looks like: ID#1 - value1, value2, value3 ID#2 - value1, value4, value6 ID#3 - value2, value4, value5 Here are the DAX formulas I've tried using, in addition to others I can't remember at the moment. 1. List of BMP observations = VAR __DISTINCT_VALUES_COUNT = DISTINCTCOUNT(FindingsFacilityVisualList[bmpObs]) RETURN CONCATENATEX( DISTINCT( FILTER(FindingsFacilityVisualList, FindingsFacilityVisualList[bmpObs] <> BLANK() ) ), FindingsFacilityVisualList[bmpObs], ", " ) Results in duplicated values: ID#1 - value1, value2, value3, value1, value2, value3, value1, value2, value3 ID#2 - value1, value4, value6, value1, value4, value6, value1, value4, value6 ID#3 - value2, value4, value5, value2, value4, value5, value2, value4, value5 --------- 2. List of BMP observations = VAR __DISTINCT_VALUES_COUNT = DISTINCTCOUNT('FindingsFacilityVisualList'[bmpObs]) RETURN CALCULATE(CONCATENATEX( DISTINCT( FindingsFacilityVisualList[bmpObs]), FindingsFacilityVisualList[bmpObs], ", " )) Results in a comma before the first value: ID #1 - , value1, value2, value3 ID #2 - , value1, value4, value6 ID #3 - , value2, value4, value5 --------- 3. List of BMP observations = CONCATENATEX( CALCULATETABLE( VALUES(FindingsFacilityVisualList[bmpObs]), ALLEXCEPT(FindingsFacilityVisualList, FindingsFacilityVisualList[bmpObs]), NOT ISBLANK(FindingsFacilityVisualList[bmpObs]) ), FindingsFacilityVisualList[bmpObs], ", " ) Results in incorrect values: ID#1 - value1, value2, value3, value4, value5, value6, value1, value2, value3, value4, value5, value6, value1, value2, value3, value4, value5, value6 ID#2 - value1, value2, value3, value4, value5, value6, value1, value2, value3, value4, value5, value6, value1, value2, value3, value4, value5, value6 ID#3 - value1, value2, value3, value4, value5, value6, value1, value2, value3, value4, value5, value6, value1, value2, value3, value4, value5, value6 ID#4 - value1, value2, value3, value4, value5, value6, value1, value2, value3, value4, value5, value6, value1, value2, value3, value4, value5, value6 ID#5 - value1, value2, value3, value4, value5, value6, value1, value2, value3, value4, value5, value6, value1, value2, value3, value4, value5, value6 *ID#4 & #5 should not have values associated with them --------- 4. List of BMP observations = CONCATENATEX ( FILTER ( FindingsFacilityVisualList, LEN ( FindingsFacilityVisualList[bmpObs] ) > 0 ), FindingsFacilityVisualList[bmpObs], ", " ) Results in Error: Can't display the visual. --------- Any suggestions for alternate ways to get to my desired end result?
Solved
HoppeG76
2 years ago Place DAX Commands and Tips
3.4KViews
0likes
7Comments
Remove leavers/users in SQL possible?
Hi all We're cleaning up our licenses and i found a bunch of users, that have still a role assigned but do not work anymore in our company. What did I do? I ran this query, this gave me all the users that are somehow on the Database. select u.UserName, r.RoleName, r.Description, c.Path, c.Name from dbo.PolicyUserRole pur inner join dbo.Users u on pur.UserID = u.UserID inner join dbo.Roles r on pur.RoleID = r.RoleID inner join dbo.Catalog c on pur.PolicyID = c.PolicyID order by u.UserName A couple of them are not anymore working. Is there a way to remove this users via another query or have i to remove them manually from the Power BI Frontend? Many thanks Brani
Solved
Brani
2 years ago Place Report Server
1.6KViews
0likes
7Comments
PowerBI Gateway log sql
I'm having problems with the Powerbi and sql management studio because I can't locate reports or datasets that weigh down my sql server with report queries that are no longer under my control I would like to sparer if there is a way to locate these queries using the dilog files of the powerbi gateway by cross-referencing with the sql activity monitor data
Solved
FrancescoRola
2 years ago Place Report Server
2.3KViews
0likes
5Comments
Report server database is getting larger and larger
hi guys i have a power bi report server and its data base is getting larger and larger. as i had a look on it, the biggest table is CatalogItemExtendedContent. is there anyway to reduce its size buy deleting some useless data? and im wondering what cause this table to be huge in size?
Solved
alisoroush7
2 years ago Place Report Server
2.1KViews
0likes
2Comments