INTERSECT between 2 Tables with different Filter Values
Hi Everyone, I would appreciate any assistance you could provide. I have two tables that share a Many to Many relationship. The first is a record of all activities created by our staff and the second is a list of all policies sold. (The actual tables are considerably larger and contain many more columns.) Activities: need to filter for only TF activities Client Code Activity Type Client Name A123 TF Adam B229 TF Bob C234 CT Charles E762 NO David A123 TF Adam B229 NO Bob C234 CT Charles A123 CT Adam E762 TF David Policies Sold: need distinct policy numbers Client Code Policy Number Premium A123 11111 $ 100.00 B229 22222 $ 300.00 C234 33333 $ 200.00 E762 44444 $ 900.00 A123 11111 $ 50.00 A123 55555 $ 400.00 I need to build a formula that shows a count of all policies sold only for those client codes where a TF activity was entered. If more than one unique Policy Number exists on a client code I need to count each of these, but I would not want to count duplicates of the same policy number on the same client code.
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.
return values from Intersect
Hi I'm trying to get the LeadID that intersects two tables I created this measure: Measure = VAR _StartDate = CALCULATE ( MIN ( FactMarketingFunnel[StartDate] ), ALL ( FactMarketingFunnel ), USERELATIONSHIP ( FactMarketingFunnel[StartDate], dimDates[Date] ) ) VAR _EndDate = CALCULATE ( MAX ( FactMarketingFunnel[StartDate] ), ALL ( FactMarketingFunnel ), USERELATIONSHIP ( FactMarketingFunnel[StartDate], dimDates[Date] ) ) VAR _main = CALCULATETABLE ( SUMMARIZE ( FILTER ( FactMarketingFunnel, FactMarketingFunnel[Stage] = "Leads" && FactMarketingFunnel[StartDate] >= _StartDate && FactMarketingFunnel[StartDate] <= _EndDate ), FactMarketingFunnel[Lead_ID], FactMarketingFunnel[Lead_Created_Date] ) --, ) VAR _int = CALCULATETABLE ( SUMMARIZE ( FILTER ( FactMarketingFunnel, FactMarketingFunnel[Stage] = "Opportunities Won Opps" && FactMarketingFunnel[StartDate] >= _StartDate && FactMarketingFunnel[StartDate] <= _EndDate ), FactMarketingFunnel[Lead_ID], FactMarketingFunnel[Lead_Created_Date] ) ) RETURN INTERSECT ( _main, _int ) how can I return the relevant LeadID?
Intersect on Temporary Tables
Hello, I am trying to create a calculated column in my table that specifies whether the activity occurs during a certain month. For example, I want the calculated column to say "Yes" if the Activity occurs at any point the month before the snapshot date and "No" if not. Snapshots are taken each month of all of the activities, so start and finish dates can vary between them. Here is an example table to show what I'm trying for. I figured that a formula like the following would work, but it's not quite right. Does anyone know of a solution to this? In Date Range? = var ActListDates=DATESBETWEEN('Date'[Date],Table[Start Date], Table[Finish Date]) var PriorMonthListDates = PREVIOUSMONTH(Table[Snapshot Date]) Return if(COUNTROWS(INTERSECT(ActListDates, PriorMonthListDates))>=1, "Yes", "No")Check if a value is available for all entries in other column / Intersect?
Hi there, I'm currently looking at quotes received from suppliers for various products. What I'm failing is to create a new column or measure to identify the products each of the suppliers have quoted for. Supplier Product Calculated Intersection A 2 A 4 4 A 5 B 4 4 B 2 C 4 4 Any ideas? Can the soltuion be probably enhanced by adding a filter on Supplier in order to determine column entries to be evaluated in the 3rd column / or a measure? Best Regards Tobias
Intersect function in Dynamic Matrix
Hi, I need help solving a new use case for me (marketing). I need to know how many customers visit the stores physically after browsing the company's web portals. The values should show the visits to the store only of the customers who browsed the portal in the same month, how many visit the store the following month, and so on. I have built part of the solution using the Intersect function, but I am not getting the measure to dynamically consider the start and end of each month based on a month id. In the following image you can see that 4 customers who visited the web during January 2020. It is also observed in the second table that in the same month there were 9 visits to the stores, the third table is the one that should show how many visited (of the 4 web clients) were made the same month, for this, row 1 represents the same month, row 2 represents the month of February 2020. According to the data, for the column January 2020 and the same month (row 1) there were 5 visits (customers can be repeated), the second row, that is, those who visited the second month (February 2020) should be 2 visits. Here is the pbix file that I am using to solve this headache Pbix I was forgetting something, the matriz should show the row and column totals Thanks in advance
