Direct query - add values from other table to the main table with conditions
Hi, I have created model in "direct query" mode, I dont have experience with it. (I normaly use add Column with combination formulas Calculate + Allexept... now it doesnt work) I need to add values from one column in "table B" to the "table A" for Column xID. I have this conditions: max values from "Column Order" -1 for each Column xID determine value in Column Personal ID in Table A, which is key for connect to Table B. The unique key for this situation in Table A is combination Column xID with Column Personal ID (because values from Personal xID can be repeated). I need to add value from Table B - Column Name to the new Column in Table A (in need can by measure), duplicated for same Column xID values. (Table A has over 23 milions rows)Dynamic Measure Required with two slicer
Hi Folks, I'm trying to develop the dashboard by comparing two months and their uses of two slicers. I prepared the dashboard using Excel Formulas. please help to develop Power-BI As the Raw data is attached Excel is available. please refer it Google Drive Link for Excel File Please feel free to contact for more info if required. RAW Data Period Account Value X Value Y Value Z Fix Tata 4 3 5 Fix Birla 6 7 8 Fix Adani 7 8 9 Fix RIL 8 9 10 Fix Tata 9 10 11 Fix Birla 10 11 12 Jan Adani 11 12 13 Jan RIL 12 13 14 Jan Tata 13 14 15 Jan Birla 14 15 16 Jan Adani 15 16 17 Feb RIL 16 17 18 Feb Tata 17 18 19 Feb Birla 18 19 20 Feb Adani 19 20 21 Feb RIL 20 21 22 March Tata 21 22 23 March Birla 22 23 24 March Adani 23 24 25 March RIL 24 25 26 March Tata 25 26 27 March Birla 26 27 28 March Adani 27 28 29 April RIL 28 29 30 April Tata 29 30 31 April Birla 30 31 32 April Adani 31 32 33 April RIL 32 33 34 April Tata 33 34 35 Regards, MOHIT15 min roll up to hour
Help! I'm using PowerBI RS (May 2023) with DirectQuery to Avaya CCMS database (odbc conncetion) I have 15 minute interval data that I'm trying to roll up to hourly data. I need to create an average talktime column which is calculated as [talktime] / [callsAnswered]. I can do this for 15 min intervals but I can't get it to roll up to the hour as I can only seem to have it calculate an average of an average (i.e. calculates the talktime/callsanswered for each row then sums the total together, this could be 4+ rows for every skillset every hour depending on how the data was recorded by the phone system. Since is over multiple skillsets so there will be multiple rows with the same date and time but different skill name for the row. I'd toyed with the idea of making a concatenated column with hour, skillset & date as an identifier for the skillset and the day and using that as a filter for CALCULATE where a concatenated expression of the same values had to equal the concatenated column but I don't know if that would work.EDIT it didn't. Here's a screenshot showing the layout I'm going for, the next column in the table needs to be a calculation of the filtered data on the visual's row. Here's a sample from the dataset
Getting differences between 2 outputs in Dax
Some context: I am connected Direct Query to a dataset so i cannot use calculated columns or change the dataset in any way. I need to accomplish this in dax. I have a measure that shows me all the tickets touched by "Team1" Note: The "MyTeam" table contains many different ticket numbers but the same ticket number can also repeat in the table for each team that touches it is a new row in the table. TouchedByTeam1 = CALCULATETABLE(VALUES(MyTable[Ticket]), FILTER(MyTable, MyTable[Definition] = "Team Group"), FILTER(MyTable, MyTable[Team] = "Team 1")) I have another measure that gets me al the tickets Team 1 touches but doesnt not move to another team. FirstTeam1Touch = VAR LastTouchTeam = CALCULATE( MAXX(MyTable, MyTable[Team]), FILTER( MyTable, MyTable[Start] = CALCULATE(MAX(MyTable[Start]), ALLEXCEPT(MyTable, MyTable[Number])) ) ) VAR FirstTeam1Touch = CALCULATE( MIN(MyTable[Start]), MyTable[Team] = "Team 1", ALLEXCEPT(MyTable, MyTable[Ticket]) ) RETURN IF( LastTouchTeam = "Team1", FirstTeam1Touch, BLANK() ) I have been at this for the last couple weeks and havent figured it out. I put both of these measure in the same Measure as variables and have been trying to use "Except" to get the difference but nothing seems to work. This was my last attempt with everything in variables. Obvisously the Except doesnt work becuase the FirstTeam1Touch does not output a table being it is a calculate statement. VAR LastTouchTeam = CALCULATE( MAXX(MyTable, MyTable[Team]), FILTER( MyTable, MyTable[Start] = CALCULATE(MAX(MyTable[Start]), ALLEXCEPT(MyTable, MyTable[Ticket])) ) ) VAR FirstTeam1Touch = CALCULATE( MIN(MyTable[Start]), MyTable[Team] = "Team 1", ALLEXCEPT(MyTable, MyTable[Ticket]) ) var TouchedByTeam1 = CALCULATETABLE(VALUES(MyTable[Ticket]), FILTER(MyTable, MyTable[Definition] = "Team Group"), FILTER(MyTable, MyTable[Team] = "Team 1"), FILTER(MyTable, MyTable[Created] >= EOMONTH(TODAY(),-2)+1 && MyTable[Created] < EOMONTH(TODAY(),-1)+1)) var total = CALCULATETABLE(EXCEPT(TouchedByTeam1, FirstTeam1Touch)) Any help is greatly appreciated.
Power BI report server Direct query: getting data form one direct query to another direct query
Hi Team, I have 3 tables one with Employee detail(employee ID, Name, Email columns) --> this table accessiable to all. data_access (employee and city) and another table with Country_revenue (Country, continent, revenue amount columns) deatils --> these table is accessible only for specific account when we pass the employee Id in the SQL pacakge(Pkg_data.country_data (Email)) I tried 1. DAX fucntion Userprincipalname to use as a parameter, but direct query (power editor) doesn't support it. 2. list.max but 'Failed to save modifications to the server. Error returned: 'DirectQuery partition 'xxxx-x56565x7575757575x757575' has '2' datasource reference(s) in its expression which is not allowed. '' Can you please help, how to pass Email into this?
Calculating over all run time for each date
Hi, I have used this below query to get the overall run time (excluding overall intervals) for a single date. But how to get the run time for each of the batch_dates as im representing in graph visuals - the computed data is incorrect. By removing the condition SchedulerInfo[BATCH_DATE]=ALLSELECTED(TenantDetails[BATCH_DATE]) also not helps, Query used: xDayendTimeALL = IF(ISBLANK(MAX([PRCS_START_TIME])),BLANK(), VAR __Table1 = ALL('SchedulerInfo') VAR __Table1_1 = FILTER(__Table1,IF( SchedulerInfo[xCoreSystem] IN VALUES(INTRL_TBL_CORE_SYSTEM[xCoreSystem]) && SchedulerInfo[BATCH_DATE]=ALLSELECTED(TenantDetails[BATCH_DATE]) && SchedulerInfo[LDBID_INL]=ALLSELECTED(TenantDetails[LDBID_INL]) && SchedulerInfo[LDBID]=ALLSELECTED(TenantDetails[LDBID]) && SchedulerInfo[PRCS_STATUS] in values(PRCS_STATUS[PRCS_STATUS]) && SchedulerInfo[xProcessModule] IN VALUES(INTRL_TBL_PROCESS_MODULE[xProcessModule]) &&SchedulerInfo[PROD_LNE] IN VALUES(INTRL_TBL_PROD_LNE[PROD_LNE]),1,0)) VAR __Start = MIN(SchedulerInfo[PRCS_START_TIME]) VAR __End = MAX(SchedulerInfo[PRCS_END_TIME]) VAR __Table = GENERATESERIES(__Start,__End,1/24/60) VAR __Table2 = GENERATE(__Table,__Table1_1) VAR __Table3 = ADDCOLUMNS(__Table2,"Include",IF( [Value]>=[PRCS_START_TIME] && [Value] <= [PRCS_END_TIME] ,1,0)) VAR __Table4 = GROUPBY(__Table3,[Value],"Minute",MAXX(CURRENTGROUP(),[Include])) VAR cTime=SUMX(__Table4,[Minute]) VAR _hrs = QUOTIENT ( cTime , 60 ) VAR _mins = INT ( cTime - _hrs * 60 ) VAR _sec = MOD ( cTime , 1.0 ) * 60 RETURN FORMAT(_hrs,"00")&":"&FORMAT(_mins,"00") )Get the average of a column value based on total count of values in ID column
Hi, In a table i have column called "Object" which runs multiple times in a day and that runtime is captured in "StartTime" & "EndTime" columns, and the difference between the start and end time is captured in the "Duration" Column. Each object will be having multiple runid's which is captured in "RunID" column which is a unique value. For eg: Object AAA has two runid's called "111,222" and Object BBB has one runid called "333". Now i would like to get the average of each duration by count of that particular runid which is captured in "New Duration" column. For eg: The count of 111 runid is '10'. so for all those durations with runid 111 should be divided by the 10. The count of 222 runid is '6'. so for all those durations with runid 222 should be divided by the 6. The count of 333 runid is '8'. so for all those durations with runid 333 should be divided by the 8. Sample screenshot for above query: So since my Duration is "10" and total count of runid (111) is 10. So 10/10=1 that is my newduration.Similarly for all durations with runid's 111 should be divided bt 10. How to achieve this using DAX query?? I am connecting to this table in powerbi via SQL direct query mode. Thanks.
