Performance issue with crossfilter function
Could you please guide how to resolve performance issue with my measure in the below case: " Sales Amt = CALCULATE( SUM(sales_table[sales_amt]), CROSSFILTER(slicer_table_sales_at_prod[cust_id], bridge_slicer_tables[cust_id], Both), CROSSFILTER(slicer_table_spend_at_prod[cust_id], bridge_slicer_tables[cust_id], Both) ) " In the sample file(which is just a subset of larger model but exact same relationship nature of the table used), I have use-case of this report to work like: 1. From the first set of slicer (1. Customers that belong to), user can select a condition that will determine dimension values in the report. Say, select those users that belong to a particular Geo and having non-zero SALES for selected products in a particular quarter [and having non-zero SPEND for selected products in a particular quarter]. 2. Now KPI slicers will further slice the to show KPI (SALES) values for the above selected customers for a paricular Quarter [made from selected channel] Please note that one of the use-case should return values in the table like return SALES values for Q1-24(20241) for those customers that have non-zero sales in Q4-23 & non-zero SPEND in Q1-24. But in another case I might ask to return SALES values for Q1-24(20241) for those customers that have non-zero sales in Q4-23 & no condition for SPEND. My alternate approach using SUMMARIZE fails for the later use-case where I don't want to involve table 'slicer_table_spend_at_prod'. I understand that crossfilter only engage the table when a values is selected in the 'Cust with SPEND>0' slicers but SUMMARIZE function is always creating a temp table on 'slicer_table_spend_at_prod' to filter the fact records. Reasoning behind the alternate solution to crossfilter approach: in the final form of our report we have 6 tables of type slicer_table_* nature and the visual is getting very slow. (specially when when we don't engage all the tables used in crossfilter means applying less slicers which is normal when user don't use any slicer at all) But this is the case where we start getting wrong values in the alternate approach using SUMMARIZE (because summarize is filtering by temp tables irrespective of slicers used or not) Could you please suggest a solution here File: https://drive.google.com/drive/folders/1JBrvkUlmloEHVSIs3HUqtEHgAPA9ysnX?usp=sharing Regards, Arjun Greg_Deckler mikeh Mikelytics
How to multiply percentage from dimension table with amount from fact table - many to many relation
Hi, I have a problem getting the appropriate data from my DAX formula. Here's my model: fact dimBank dimSecurites Fact table is a day to day table with debt amortization plan ordered by tranche (%dimSecurities) dimSecurities is a table with all specific data for the vessel, each having unique key %dimSecurities dimBank is a table where the split between multiple banks, where a %dimSeucrities sometimes occur 3-4 times depending on how many banks involved This is a simplified version of my data model: Looking at this table, the data is correct when I add other dimensions, like "Share" or even better "%dimSecurities", but the total sum is incorrect as it is a result of the average share multiplied by total debt... However, I would like to show these data graphical in a barchart, but then I cannot add the aggregation, hence the total sum is being used, wich is incorrect, 12,017,545 when the correct sum should be more like 7,961..... etc This is my DAX formula... debt by lenders = CALCULATE(SUMX('factSecurity',factSecurity[Loan Balance Start] * LOOKUPVALUE(dimT16_Valutakurs[Currency Rate], dimT16_Valutakurs[Dates], Max('Calendar'[Date]), dimT16_Valutakurs[FRA_VAL_KODE], factSecurity[Currency])), FILTER(factSecurity, factSecurity[Dates] = Max('Calendar'[Date])))* AVERAGEX(dimBankAndGuarantor, dimBankAndGuarantor[Share]) Any help is much appreciated 🙂 Thanks E
Cross filter report page based on measure selection
Hello PB Comunity, This is my first question ever. Hopefully you DAX geniuses can help me out here. I have a measure counting the new customers: Count new customers = VAR CustomersWithNewDate = CALCULATETABLE ( ADDCOLUMNS ( VALUES ( 'Sale invoices'[customer_id] ), "@NewCustomerDate", [Sale invoices - First date by partner] ), ALLSELECTED ( Customers ), ALLSELECTED ( 'Calendar' ) ) VAR CustomersWithLineage = TREATAS ( CustomersWithNewDate, 'Sale invoices'[customer_id], 'Sale invoices'[date_id] ) VAR Result = CALCULATE ( DISTINCTCOUNT ( 'Sale invoices'[customer_id] ), KEEPFILTERS ( CustomersWithLineage ) ) RETURN Result This works and I use this in a barchart on my report:   Now when I click on one of the bars of this chart I want the report to be crossfiltered to shwo only the data for those new customers. But instead of showing only the data for the new customers the report only gets crossfiltered by the selected month and not only new customers. I know that there should be a way to do this but i cannot find a way to do this. I have 3 tables: - Sale Invoices - Customers - Calendar There are two excisting relationships: - 'Sale invoices'[date_id] = 'Calendar'[date_id] - 'Sale invoices'[customer_id] = 'Customers'[customer_id] I would be very greatfull if someone could help me out here! Thanks in advance!A fact table getting info from dimension table and fact table then grouping & distinct count
Hi , My scenario is 2 fact tables (point & trans) connecting to a dimension table (profile) point table is filtered by calendar table (e.g. 3-Aug-2023 to 4-Aug-2023), there are 124 members with this date selection a. getting the regist date of these 124 members from profile table b. getting the last order date from trans for these 124 members from trans table 1. when regist date of member >= 3-Aug-2023, would call this member group as Group A and get the distinct count of the members 2. when regist date of member < 3-Aug-2023 and last order date >= 3-Aug-2022, would call this membes group as Group B and get the distinct count of the members 3. when regist date of member < 3-Aug-2023 and last order date < 3-Aug-2022, would call this member group as Group C and get the distinct count of the members 4. when regist date of member < 3-Aug-2023 and last order date is null, would call this member group as Group D and get the distinct count of the members have written 2 measures [_Last_Order_Date], [_Last Order Date (for points member)] about this, but not sure how to proceed for [_Historical Purchase Group Count] have uploaded pbix file to onedrive , could anybody help to get this work? fact-bridge-fact.pbixCrossfilter and row level security
Hello, I have the following model for a report: I've been asked to implement RLS and I'm having some trouble figuring out the correct method. In my original model table 1 does not exist and is not required. Table 1 is added for RLS amd contains a list of users email addresses, and the IDs which represent the rows I want the fact table, table 2, to filter to. One ID can be accessed my multiple users in table 1 which is why it is a many>many relationship. The RLS is setup to use Userprinciple name to filter table 1 and subsequently table 2. This element seems to work fine, as I can see when RLS is applied, table 2 does filter to the correct IDs the person in table 1 has access to. Due to table 2 being the fact table and on the many of the many>one side, it does not pass these filters through to the associated dimension tables. I have been reading into this as it is new to me, and thought I could use crossfilter within my measures to make the filters flow through to the dimension tables. As an example, for a measure that counts the rows in table 3 I thought I could try something like the below to count the total rows in table 3, but only for the rows which match those filtered in table 2: CountRowsTable3 = CALCULATE( COUNTROWS(Table3), CROSSFILTER('Table2'[ColumnA],Table3[ColumnA],Both) When I do this calc I still get the total number of rows in table 3 without the RLS applied. Grateful for any steers on the best way to do this, or if I am down totally the wrong track happy to be told so too! Thank you
Measure responds inconsistently when added to table
I have a measure that calculates if an entry falls within a date range and, if it does, returns a 1. However, when applied to a table it seems to be totally inconsistent. See below - all rows should return a 1 Any suggestions as to what is causing this discrepency would be appreciated. Thanks Please note - this is a continuation from an unsolved thread here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Measure-not-calculating-all-rows-on-a-table/m-p/2480189#M67843Check if any value in each row appears in SELECTEDVALUE row
Hello, all. I had a student ask whether it was possible to filter a table visual for all rows that match any value in the row selected in another table visual. I said yes! and threw together this example (new link), but this requires the column names to be specified in the expression, like below: Shared Value? = IF( MIN('Customers Copy'[City]) = SELECTEDVALUE(Customers[City]) || MIN('Customers Copy'[State]) = SELECTEDVALUE(Customers[State]), // etc etc 1, 0) I'm wondering if it's feasible to do this dynamically, so that all columns of a table could be checked against all values associated with SELECTEDVALUE. Some sort of VALUES(ROW(...)) IN SELECTEDROW(...) if you will. I know DAX doesn't support traditional loop structures, but I suppose this is a bigger question about dynamically referring to columns in DAX tables (if only we had some sort of equivalent to Expression.Evaluate !!) .pbix file is linked above. Please note the calculated Customers Copy table (I'm also wondering if this is feasible without the copy table) and Shared Value? measure (used in a filter on the second table). Thanks for any ideas!Summarize table with dynamic filtering
Hello Team, I need some major help to solve this issue. I have a summarized calculated table that I need to be able to filter dynamically. Below is a sample of the calculated table that I have a union joining on. The first table in the union is "Brand EPA" and is an average from the full Brand EPA table. Below is the full Brand EPA table where the summarized calculated table is pulling from however I need a way to have the results be dynamic when I filter on Sex, Age, Education, Income and BrandOwn. This will change the average from being static to dynamic in the summarized table. Thanks all, Sean
Why do I always have to add CROSSFILTER + NONE when I use USERELATIONSHIP ?
Hi, When I read documentation, blogs and forums, it seems like USERALATIONSHIP can be used alone. It seems so simple yet I cannot get it to work without having to add a CROSSFILTER to disable the default active relationship. Here's my usecase : Basically I get it to work in the T3 test by explicitly disabling the default relationship using CROSSFILTER + NONE. But I don't understand why the T2 option does not work. Default active relationship is Programme[Code] = RelanceProgramme[Programme] The inactive relationship is Programme[DecReference] = RelanceProgramme[Programme] Here's the row from the Programme dimension. DEFINE // BASELINE using the default active relationship VAR _t1 = CALCULATETABLE ( RelanceProgramme, Programme[Code] = "NWE.0F" ) // Attempt #1 to activate the second relationship (FAIL) VAR _t2 = CALCULATETABLE ( RelanceProgramme, USERELATIONSHIP ( Programme[DecReference], RelanceProgramme[Programme] ), Programme[Code] = "NWE.0F" ) // Attempt #2 to acticate the second relationship (SUCCESS) VAR _t3 = CALCULATETABLE ( RelanceProgramme, USERELATIONSHIP ( Programme[DecReference], RelanceProgramme[Programme] ), CROSSFILTER ( Programme[Code], RelanceProgramme[Programme], NONE ), Programme[Code] = "NWE.0F" ) Thanks --mo
