sql

18 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
11 months ago Place DAX Commands and Tips
Views
0likes
Comments
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
Views
0likes
Comments
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
Views
0likes
Comments
if data not exist in base display blank or 0
Hello guys, i have some column from database in Direct Query Here the result that i want : Id Point email 1 400 [email protected] 2 [email protected] 3 600 [email protected] Here the explanation what i have : For the Id 2 , i want to display empty or 0 for the value "Point" but in my base there is no data for this Id , he has no point then the row dosnt exist, cause of that the row with Id 2 dosnt appear in Power BI , is it possible to have a dax formula to fix that ? (I precise the column point its a Sumx formula). Thank you, if you need more informations dont hesitate please.
ItSupportElm
3 years ago Place DAX Commands and Tips
Views
0likes
Comments
Power BI version of 'Case When' using "Switch" Function
Hi guys - i'm new to dax and am having a bit oftrouble with what I would like to think should be something simple. Essentially i have paramaters for each bucket listed below and I want those to dynammically fall into one column. I think I've done most of the hard part but I can't seem to put the right measure together. - Is this possible with a measure? I don't think i can add a numeric parameter in the calucalted column in power query so i'm kinda stuck here. Hopefully this is as simple for one of you as it would be for me in sql. What am i doing wrong here? ENLR Delta = SWITCH( TRUE(), FILTER('Application','Application'[Fico Range] = "640-679"),([Net Loss Ratio] - [640-679 Value]), FILTER('Application]','Application'[Fico Range] = "680-699"),([Net Loss Ratio] - [680-699 Value]), FILTER('Application','Application'[Fico Range] = "700-739"),([Net Loss Ratio] - [700-739 Value]), FILTER('Application','Application'[Fico Range] = "740-799"),([Net Loss Ratio] - [740-799 Value]), FILTER('Application','Application'[Fico Range] = "800+"),([Net Loss Ratio] - [800+ Value]), 0 )
Solved
OneData
3 years ago Place DAX Commands and Tips
Views
0likes
Comments
Trying to get a total with several requirements
I am trying to get a total of Agreement Hours but need to filter several requirements. Here is the SQL sum(case when Time_Entry.Agr_Header_RecID is not null and AGR_Hours = Hours_Bill and Billable_Flag = 'True' and Time_Entry.Company_RecID <> 250 then AGR_Hours else 0 end) AGR_Hours_Total and here is what I have tried to do in DAX Actual_AGR_Hours_total = Calculate(Sum(Switch(not(ISBLANK('Time Table'[AGR_Header_RecID])) & 'Time Table'[AGR_Hours] = 'Time Table'[Hours_Bill] & 'Time Table'[Billable_Flag] = True & 'Time Table'[company_RecID] <> 250, 'Time Table'[AGR_Hours] , 0))) The table names are different from the SQL and what I pulled into Power BI fyi. 'Time Table' is correct.
Solved
gboss87
3 years ago Place DAX Commands and Tips
Views
0likes
Comments
SQL to DAX
Two tables, Table A has User/Event/EventDate. Table B has User/InteractionDate. I would use the following SQL Statement to get a one to many table; Select A.User, A.Event, A.EventDate, B.User, B.InteractionDate From A, B where A.User = B.User AND ( B.InteractionDate >= A.EventDate AND B.InteractionDate <= DateAdd(A.EventDate,30) ) the A.User = B.User is related and can be in a JOIN instead of in the WHERE Clause. any help to convert this to a DAX or PowerQuery process and get the same results?
Solved
Anonymous
4 years ago Place DAX Commands and Tips
Views
0likes
Comments
I need help in translating the following SQL to DAX
SQL 1: CONVERT(VARCHAR(8),CASE WHEN (SUM(CASE WHEN InResult IN ('I2','I5','I8','I9','I18','I25','I31','I32','I33') AND FinalFate IN ('Q10','Q24','Q26') AND DATEDIFF (s, RouteToQTime, AgentAnswerTime) > 0 THEN DATEDIFF(s, RouteToQTime, AgentAnswerTime) ELSE NULL END)) IS NULL OR (SUM(CASE WHEN InResult IN ('I2','I5','I8','I9','I18','I25','I31','I32','I33') AND FinalFate IN ('Q10','Q24','Q26') THEN 1 ELSE NULL END)) IS NULL THEN 0 ELSE DATEADD(ss,SUM(CASE WHEN InResult IN ('I2','I5','I8','I9','I18','I25','I31','I32','I33') AND FinalFate IN ('Q10','Q24','Q26') AND DATEDIFF (s, RouteToQTime, AgentAnswerTime) > 0 THEN DATEDIFF(s, RouteToQTime, AgentAnswerTime) END)/SUM(CASE WHEN InResult IN ('I2','I5','I8','I9','I18','I25','I31','I32','I33') AND FinalFate IN ('Q10','Q24','Q26') THEN 1 END),0) END,108) AS [RC HoldTime] SQL 2: SELECT CASE WHEN (GROUPING(QueueName)=1) THEN 'Grand Total' ELSE QueueName END AS Queue, Please note, we are on SQL Server 2016 (SP3) (KB5003279) and IN Operator in DAX is not supported in SQL Server 2016. Thanks in advance.
ranjanrkl
4 years ago Place DAX Commands and Tips
Views
0likes
Comment
DAX syntax for SQL query
Hi, I have a SQL query for my database which I use for grouping entries in a survey table by specific departments. As a result I want a simple table that looks somewhat like my SQL query. SQL query select md.DEPARTMENT, count(distinct mpqr.MEDIC_UID) from `20583`.MEDIC_PROPERTY_QUESTION_RESULT mpqr left join `20583`.MEDIC m on mpqr.MEDIC_UID = m.UID left join `20583`.MEDIC_DEPARTMENT md on m.DEPARTMENT_UID = md.UID group by md.DEPARTMENT order by 2 desc Output: How do I accomplish to get a table in Power BI? In the end I only want to use a simple measure that gives me the [DEPARTMENT] for MAX([count(distinct mpqr.MEDIC_UID]) which means '525' in this specific case. thank you
Solved
deboec
4 years ago Place DAX Commands and Tips
Views
0likes
Comments
SQL to DAX conversion
I am trying to get the equivalent logic in DAX for below SQL declare @date datetime declare @warehouse nvarchar(225) declare @province nvarchar(225) set @Transdate = '2021-09-18 00:00:00.000' set @province = 'BC' set @warehouse = 'BC-01' ----- THIS CTE RANKS THE ITEMS TO GET THE MOST RECENT category ----------- with main1 as ( SELECT * , ROW_NUMBER() OVER(Partition by itemid, warehouseid ORDER BY transdate DESC) AS rn FROM [EDW].[FACT].[InventoryBalancedet_2] dt WHERE province = @province and warehouseid = @warehouse and transdate <= @Transdate ), ------ THIS CTE RANKS amount of an item by warehouse ----------- main2 as ( select sum(Amount) as AMOUNT , ItemID ,warehouseid FROM [EDW].[FACT].[InventoryBalancedet_2] WHERE province = @province and warehouseid = @warehouse and transdate <= @Transdate group by ItemID , warehouseid ), ------------ COMBINING THE QUERIES TOGETHER -------------------- main3 as ( SELECT m2.AMOUNT, m2.warehouseid , m1.Category FROM main1 m1 inner join main2 m2 on m1.warehouseid = m2.warehouseid and m1.ItemID = m2.ItemID and m1.rn = 1 ) select sum(m3.total) ,m3.Category FROM main3 m3 group by m3.Category
Solved
amikm
4 years ago Place DAX Commands and Tips
Views
0likes
Comments