How does CALUCLATE merge the filter arguments of which one is a table and the other is a column
Hi~Dear PowerBI community I met a scenario that got some results I couldn't explain well, making me doute and confused. I think there must be something wrong in my understanding about how CALCULATE merge the filter arguments. I've done all I can but failed to figure it out. Let me try to show this question, hoping someone would give me some hints. ---- There are two tables : <Dfact> and <Ddim> , and a relationship between them <Dfact> ID SubCategory 1 SC1 2 SC1 3 SC2 4 SC2 5 SC3 6 SC3 <Ddim> Category SubCategory C1 SC1 C1 SC2 C2 SC3 C2 SC4 C3 SC5 I've learned that when a table is transformed in a filter, the filter contains the expanded version of the table. Therefore, the following measure calculates the number of <Ddim> referenced in the <Dfact> table: CALCULATE ( COUNTROWS(Ddim), Dfact) Then I added an additional column filter argument and got the same result: CALCULATE ( COUNTROWS( Ddim ), Dfact, Dfact[SubCategory] = "SC1" ) I was trying to make analyses of the filter context and I knew I could explain it as below: the columns filter used as the second argument is a column not a table, and there's no such a column in the expanded version of <Ddim> table, so the column filter won't filter the <Ddim> table . While the first augument <Dfact> can filter the <Ddim> due to the expanded version of the <Dfact> table. But things got wired while I analyzed it another way with more detail into the merge of the filter arguments. I read The Definitive Guide to DAX and it told me these: The multiple filter arguments of CALCULATE are always merged with a logical AND. CALCULATE merges its filter arguments with an intersection. Given two filter contexts, A and B, the intersection of A and B is computed by adding the filters in A to the filters in B. According to these rules, I analysed it as below: FILTER A (the first augument): Dfact (expanded version containing all the columns of <Ddim>), a table with many columns FILTER B (the second augument): Dfact[SubCategory]="SC1" or FILTER(ALL(Dfact[SubCategory]), Dfact[SubCategory] = "SC1"), a table with only one column If I add filters B to filters A, I will get the result filter C as below, is it right? If I were right, the first argument of CALCULATE should be calculated in this new filter context and get a result as 1( only one row contains "SC1" in <Ddim>), which is completely different from the previous result as 3 . So there must be something wrong in my understanding about how CALCULATE merge the filter arguments, even worse about what the filter really is. I tried some tests and got these results The result of test1 is the table <Dfact> with only "SC1" in the rows, identical to the analysis by merge filter argument above. The result of test2 is the table <Ddim> with the subcategories referenced in the <Dfact> table, identical to the analysis by the expanded talbe and filter propagation. The result of test3 shows the number of rows of <Dfact> and <Ddim> in the exactly identical filter context. I thought CALCULATE creates the new filter context before caluclates the first argument, but the result of test3 confused me more, wandering are there two filter contexts affecting the two COUNTROWS respectively ? Furthermore, I checked the SE queries, there was "[SubCategory] = 'SC1' " in the WHERE statement of the xmSQL of test1, whereas in the xmSQL of test2, "[SubCategory] = 'SC1' " is totally discarded in the 3 SE queries. I can't get more useful information in the SE query, cause I knew few about it. I got stuck here. Please give me some helps.Data from one or another column but merged
Hi, I have two columns with the same name (Business Region) in two entities (contact and account) If it s a contact then Business Region from contact is populated and Business region from Accoutn is blank and the way around. CONCATENATE = SELECTEDVALUE(account[Business Region]) & SELECTEDVALUE(contact[Business Region]) I need basically to concatenate the data. Also to mention is direct query not import of data. Can you help please. Thank you More details: So we have 3 entities: contact, account and donation. When the amount from donation is linked to contact (so it's a person who donated) then it is populating the Business Region for contact but is blank for account. And when the donation is coming from an account (a business/organization etc) the business region is populated for account entity and is blank for contact. I basically need to get those two fields of Business Region populated in one field. So as example let s say: Jon Doe donated 6 euro and he is from London (only contact Business Region field is populated as this is contact) and Analytics ISO company donated 7 and location is Belfast (only account Business Region field is populated as this is account) My output should be: Donor Business Region Amount Jon Doe London 6 Analytics ISO Belfast 7 Hope now makes sense.
2 Tables _ Combine _ RANKX
Hi guys, I have a quick question, maybe you can help. Table 1 Code Plan Group Arrival date other column 1 CCC/a Windows 1/1/2020 2 CCC/a Windows 1/1/2020 3 CCC/a Lock 1/1/2020 101 CCC/a Lock 1/1/2010 203 CCC/b Door 2/1/2010 205 CCC/b Door 2/1/2010 206 CCC/b Door 2/1/2010 205r BBB/a 2/1/2010 Table 2 Plan Group Class Sold Date CCC/a Windows Hard 1/1/2000 CCC/a Lock Medium 1/1/2005 CCC/b Door Medium 20/04/2020 What I can do is to create table visual like this Code Class Sold Date Plan 203 Medium 20/04/2020 CCC/b 205 Medium 20/04/2020 CCC/b 206 Medium 20/04/2020 CCC/b What I would like to do is to do this: Code Class Sold Date Plan 1 Hard 1/1/2000 CCC/a 2 Hard 1/1/2000 CCC/a 3 Medium 1/1/2005 CCC/a 101 Medium 1/1/2005 CCC/a Could you help me with that please ? Plan is calculated column with Rankx.
Refreshing queries is so slow (15 to 20 min)
Hell all, I'm using three csv folders as data sources for my database created in EXCEL with power query. When i refresh my main query, it takes so much time (from 15 to 20 minutes). I guess it is related to the merge function that i used to relate two tables. you'll find below the M code used to merge the two table. Any tips to improve the speed of this query? thank you by advance. let Source = Table.NestedJoin(#"HISTORIQUE MB52", {"CLE", "Date"}, #"UPS STOCK", {"material_Sloc", "Date"}, "UPS STOCK", JoinKind.LeftOuter), #"UPS STOCK développé" = Table.ExpandTableColumn(Source, "UPS STOCK", {"Account", "FSL", "Ref 5 ", "Designator", "UPS QTY"}, {"Account", "FSL", "Ref 5 ", "Designator", "UPS QTY"}), FilterRows = Table.SelectRows(#"UPS STOCK développé", each [UNR] <> 0 or [Blocked] <> 0 or [Returns] <> 0 or [UPS QTY] <> null) in FilterRows
Merge two rows (having a different cell value) into a single row; overwrite values for text
Hello, I want to merge rows when duplicate "Task" value is found; and overwrite text from one "Response" value to another cellwhen Scaled Value is "-1.00". For example, see below two rows: Instance Assigned to Taken on Task Response Scaled value Assignment group Comments and Work notes ABC123 Nick 12-Mar-21 03:31:15 ANC0691115 Very Satisfied 4.00 Electrical Created from a similar incident INC0690512 ABC123 Nick 12-Mar-21 03:31:15 ANC0691115 Nothing, easy going and uncomplicated. Thank you very much! -1.00 Electrical Created from a similar incident INC0690512 Once the rows are merged, it should look like below: Instance Assigned to Taken on Task Response Scaled value Assignment group Comments and Work notes ABC123 Nick 12-Mar-21 03:31:15 ANC0691115 Nothing, easy going and uncomplicated. Thank you very much! 4.00 Electrical Created from a similar incident INC0690512 Can you please suggest what DAX formula/ Power Query shall be used? Thank you so much in advance!Calculate daily budget from monthly budget
Hello, I have a budget that is monthly and I want to convert that into daily values, simple in SQL but I'm new to DAX. The ContractBudget table is imported from SQL The DateDax is a date table generated by DAX. I currently have no relationship between the tables. In SQL I'd join ContractBudget.Date=DateDax.FirstDayOfMonth to get a budget value for every day of the month but PowerBI says that's a many to many, and there be grumpy dragons ahead. How do I get a budget value for each day where I divide DecimalBudget by Days In Month to arrive at my daily value? I'd do a merge in Power Query but calculated tables don't show up there. Is that correct?
Tables with Two Key
Hello, I Have two tables the first includes the Revenue Details and the other one claims, the tables looks something similar to the below, Medical Record No Visit Number Visit Date Name Claim ID Phamracy Claim ID Invoice Amount XX VAN100 1 Jan 20 John CL1000 150 YY VAN200 1 Jan 20 Adam CLP2000 250 ZZ VAN300 5 Jan 20 Lawrance CL1500 CLP2500 500 And the other table Include the the Claims Details as follow: Claim ID Payment Date Payment Amount CL1000 2 Feb 20 150 CLP2000 5 Feb 20 200 CL1500 3 March 20 200 CLP2500 7 Feb 20 300 Im trying to create a relation between the two tables but since there is two coulmns in the first table as the primary key and only one in the second table i could not get, im looking to build a summary that looks like Visit Number ( Table 1 ) Claimed Amount (Table 1) Paid Amount (Table 2 ) VAN100 150 150 VAN200 250 200 VAN300 500 500DAX Command to merge multiple Columns into One column
Hi, Lets picture a table structure as this: Month Budget_Q1 Budget_Q2 Budget_Q3 Budget_Q4 ---------------------------------------------------------------------------- Jan 100 ..... ....... ....... Feb 100 ..... ...... ........ Mar 100 ..... ........ ....... Apr 110 115 ....... ....... May 110 115 ...... ....... Jun 110 115 ........ ....... Jul 120 122 125 ...... Aug 120 122 125 ...... Sep 120 122 125 ..... Oct 110 115 120 115 Nov 110 115 120 115 Dec 110 115 120 115 ... I'm trying to merge a selection of Rows to get the original budget for that quarter. That means the table should look like Month Budget_Summary ------------------------------------- Jan 100 (From Column Budget_Q1) Feb 100 (From Column Budget_Q1) Mar 100 (From Column Budget_Q1) Apr 115 (From Column Budget_Q2) May 115 (From Column Budget_Q2) Jun 115 (From Column Budget_Q2) Jul 125 (From Column Budget_Q3) Aug 125 (From Column Budget_Q3) Sep 125 (From Column Budget_Q3) Oct 115 (From Column Budget_Q4) Nov 115 (From Column Budget_Q4) Dec 115 (From Column Budget_Q4) This is basically an equivalant to SQL code below: SELECT BUDGET_Q1 FROM table_x WHERE MONTH IN ("Jan","Feb","Mar") UNION SELECT BUDGET_Q2 FROM table_x WHERE MONTH IN ("Apr","May","Jun") UNION SELECT BUDGET_Q3 FROM table_x WHERE MONTH IN ("Jul","Aug","Sep") UNION SELECT BUDGET_Q4 FROM table_x WHERE MONTH IN ("Oct","Nov","Dec") How may I achieve this in DAX? Thanks, Usher
