sql

18 Topics

Calculate: A table of multiple values was supplied where a single value was expected.
Hi guys, Last year my collegue wrote a dax formula for my BI report. Unfortunately he is no longer working at my firm. Couple of days ago the datasource of my report was moved into the cloud. I already changed the location of the datasource in my report, but the Dax formula he wrote for me doesn't work since then. I tried a couple of things but it wouldn't work. The error is: A table of multiple values was supplied where a single value was expected. Can someone help me? Thank you! The dax formula: EmployeeKey = CALCULATE( DISTINCT('DimEmployee'[EmployeeKey]), FILTER('DimEmployee', 'FactActualHoursAll'[TT_EMP_ID] = 'DimEmployee'[EmployeeId] && 'FactActualHoursAll'[TT_date] >= 'DimEmployee'[RowEffectiveDate] && 'FactActualHoursAll'[TT_date] <= 'DimEmployee'[RowExpirationDate] ) )
Solved
Anonymous
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
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
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
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
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
5 years ago Place DAX Commands and Tips
Views
0likes
Comments
Using filter in MDX
Hi all I want to filter an MDX query base on the sum of a integer field [Employee Wage Hours] at the year level, but displaying weekly summation results. Therefore I found how to filter taking into account the sum of [Employee Wage Hours] on a whole week (easy): SELECT NON EMPTY{ [Measures].[Employee Wage Hours] } ON COLUMNS, FILTER (([03-Employee Info].[Employee Name].[Employee Name], [02-Week Period].[Week No].[Week No], [02-Week Period].[Hierarchy].[Year] ), SUM([Measures].[Employee Wage Hours]) > 20 ) ON ROWS FROM ( [Model]) Then I have the employee/week/year for which there is more than 20 [Employee Wage Hours] a week, for each week. But I cannot see, since I have a "week" row, how to have the same 3-uples for which there is more than 20 [Employee Wage Hours] a year. Do I have to use a WITH clause ? Thanks for your answer...
Anonymous
5 years ago Place DAX Commands and Tips
Views
0likes
Comments
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 query to DAX Measure conversion
I have the following SQL query that I have converted to DAX Measure SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY SSN ORDER BY EOMDate DESC) FROM [TransArchive].dbo.AGGR_Transaction_ChannelUtilization WHERE EOMDate BETWEEN @dteEOMBegin AND @dteEOMEnd DAX Measure ::: Index = CALCULATE(COUNTROWS('Member Channel Utilization'), FILTER(ALLSELECTED ('Member Channel Utilization') ,FILTER('Member Channel Utilization','Member Channel Utilization'[Report Date]<= EARLIER('Member Channel Utilization'[Report Date])) ,FILTER('Member Channel Utilization','Member Channel Utilization'[EDWCustomerID]= EARLIER('Member Channel Utilization'[EDWCustomerID])))) Please Note :::: ReportDate = EOMdate and SSN =EDWCustomerID I have used ALLSELECTED becasue I want the rownum value to change based on the date range filter and /or any other filter selected by the user on the PowerBI report. When I run the query I get the following message "Too Many Arguments were passed to the FILTER Function. The Maximum argument count for the function is 2".... Could you please let me know how to make this DAX work . Thanks in advance
Anonymous
5 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