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, MOHIT
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.
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.
