Model 10 program fact tables that all share the same Individuals dimension? Regular patterns break
Hi, there. We’re a non-profit with 10 community programs. Each person (IND_ID) may join any program once per year, so every fact table is IND_ID × Year. Goal – count unique individuals while letting users slice by • program-specific fields (e.g., MomsMeet[Location], SummerYouthGroups[Session]) • shared fields (Year, etc.) See mockup: Desired slicer behavior No slicers selected → show the total distinct individuals count across all 10 programs. Any slicer selected → return only the individuals that satisfy every chosen slicer (intersection). Important: Also exclude programs that don’t have a slicer applied. Current model Dim Individuals (1) → 10 program fact tables (many). Why the usual “combine facts into one unioned table” approach fails: Since each program has fields unique to that program. When you slice on that field, it excludes the rows of the other programs. Making those excluded rows not available for further filtering. And the slicers go blank. See image: Already tried / ruled out Bidirectional or Many-to-Many on IND_ID → ambiguous TREATAS / CROSSFILTER in each measure → too much maintenance Looking for: a modelling pattern that keeps other programs and slicers active Onedrive .pbix is here, I'm glad to check suggestions and report back. Thanks for any pointers! --- Moderator note: This is a fully-rewritten, clearer version of my original post (https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Count-distinct-families-across-multiple-program-fact-tables/m-p/4673648); please merge or close the earlier thread if appropriate.
Moving Benchmark
Hi All, please I have hit a little snag .. I have a formula that was supposed to calculate the water consumption weekly but the benchmark I want to be flexible depending on the number of persons available in that week. columns : Average headcount week number KPI's one person is assumed to consume a minimum of 1.5 liters weekly NW Benchmark = Calculate(sumx(FASENV,FASENV[headcount]*1.5),filter(FASENV,FASENV[Location] in{("Average Head Count - Staff House I"),("Average Head Count - Staff House II"),("Average Head Count - Staff House III"),("Average Head Count - Staff House IV"),("Average Head Count - Office")})) Kindly assist avanderschilden Anonymous HotChilli Nathaniel_CChecking Data Completeness
Hi I am new to PowerBI/PowerQuery, the following task might be trivial to some of you, but your help is highly appreciated 😁. For example: I have three columns, where each column is presented from different files Vendor Name Masterfile A Masterfile B Status A xxx xxx Complete B xxx Incomplete C Incomplete I want to develop a new column called "Status" where it will examine the completeness of column Masterfile A and Masterfile B. When BOTH columns were filled it will say "Complete" as in Vendor A When ONE column or BOTH are NOT filled it will say Ïncomplete" as in Vendor B or C I think the best approach to this is using the Switch True function of If function. I might be wrong. RegardsCopy value from one row to underlying rows
Hi all ! I have searched for a solution to this problem without luck - hopefully this issue is known to others: I have a table with date and transactions. The problem is that the date is not repeated on the rows - see example below. I am looking for the soluttion to copy the date and replace the "null" with date - until a new date is reached: My table: This should read: Thankful for any hint and suggestions ! Rgds Eirik BIF function optmization
Hello! I have a measure currently that compares a customer's "first sale date" (date of their first purchase) against my current calendar table date selections. My problem is that the DAX is taking well over 200,000ms to compute. Data Model Structure The data model I'm using has quite a bit of relationships and tables included, but I'll confine this to just the relevant tables. Tables: dim_cust (customer table) fct_cust_date (daily customer sales) vw_dim_dates (view of our date table with a "Current Date" column added to circumvent UTC time shenanigans) Relationships: dim_cust (CustID) to fct_cust_date (CustID) fct_cust_date (dateid) to vw_dim_dates (dateid) Reporting Need In my current report, I have date slicers that users can select from including Year and Month. I have a calculated column for each determining the "Current Year" and the "Current Month" so that those can be a dynamic default selection. What I'd like to be able to do, is have a visual that displays whether a customer's "first sale date" is within the dates that have been selected in the Year and Month slicers. I can get this to work, but the performance has been terrible and the visual is nearly unusable. DAX My current measure is as follows: FS Date in Selection = VAR MaxDateSelected = MAX( vw_dim_dates[date1] ) VAR MinDateSelected = MIN( vw_dim_dates[date1] ) VAR FSDate = MAX( dim_cust[dim_dates.date1] ) RETURN IF( AND( FSDate <= MaxDateSelected , FSDate >= MinDateSelected ) , TRUE() , FALSE() ) I think the main issue is that my dim_cust table is not directly connected to my vw_dim_dates table through the customer's first sale date, but this would introduce another issue. A relationship like that would limit me from displaying customers whose first sale date is outside the selected date range. Any thoughts on how I can increase the efficiency of my current measure while still meeting the reporting needs? Thank you!
Converting Mailchimp Date to an actual date
Hi. I have original data from Mailchimp for "Send Time" which is not formated as date, and has values like: "2019-05-05T13:24:46+00:00" I can't format this as a date, I get an #Error (it's currently text.) I have a new colum called Date Sent, which works with this formula: Date Sent = left('Campaign Summary'[send_time],10) which at least gets the relevant characters, which still can't be formatted as a date (#Error). I've tried changing the format in the "Modeling" tab. I've also tried doing a third column (I know this is silly) which would do this: format date sent = format('Campaign Summary'[Date Sent],"MM/YY") - which still returns only text. I only want this to customize my table for relative date filtering, which it can't do on text. Another option is to sort it by the text (which at least shows the most recent at the top), but then to limit the table to only the top ten records. Please let me know if there is a solution here. Thank you!DAX Measure to Divide row value by filtered row value from same table
I have 2 Tables Problem: I need to calculate Fair Share Index (share of product in a region/ share of its cat in the same region), which is essentially division of ratios. The first part will have the Product Sales(Dollar Sales) in a Geography for a Time Period divided by Product Sales at National level from Table 1. The second part will have the Category Sales (Cat Dollar Sales) in the same Geography for same Time Period divided by Category Sales at the National Level from Table 2 I need a new Measure in Table 1 with this formula: For each row: Index = [ (Table 1 row[Dollar Sales] / Table 1 [Dollar Sales] where [Geography] == "National" & [Time] == row[Time] & [Prod] = row[Prod]) / (Table 2 row [Cat Dollar Sales] / Table 2 [Cat Dollar Sales] where Geography == "National") ] * 100 Example: Consider the Bold and Italicized Row in above Table 1. The calculation will be as follows, Fair Share Index = [ (100/500) / (1000/10000)) ] * 100 = [ (0.2) / (0.1) ] * 100 = 200% What I have Tried: I have joined the Tables in the Data Model on Product Column with Many to Many cardinality and Table 1 filters Table 2. I have tried implementing several type of DAX queries but everything throws an error. I'm new to PowerBI especially DAX. I have done the same in Python with no issues. So any pointers on how I can achieve this would be greatly appreciated. Thanks in advance
ENDOFMONTH function not working as expected
I created a simple date table as follows: DateTable = DATATABLE( "Date", DATETIME, { {"14/2/2024"}, {"15/2/2024"}, {"16/2/2024"} } ) Then I created a new column using End of Month = ENDOFMONTH(DateTable[Date]) Result I know I can use EOMONTH which works, but am curious why this doesnt? I expect to get 29 February but it seems like its simply picking the maximum date. Or maybe I failed to understand the documentation
Calculated Column
I’m trying to set up a column that would be used as a flag, using conditionals. I have an ‘informational’ table, and another table named ‘repeatable’. There is one record per record_id in my informational table, but multiple records per record_id in my repeatable table. It’s joining on record_id (one to many). I need a conditional column created in my informational table, referencing a column (followupvisit) in my repeatable table. It would go something like this…. if followupvisit has 0 only, then "first follow up visit completed", if followupvisit includes a 0 and 1, then "2nd follow up visit completed", if followupvisit includes a 0, 1, and 2, then "all follow up visits completed", else “no follow up visits completed”. Here's an example of my data So for record_id 3, after I would create this new column, I’d have one row reflected here and it would say “2nd follow up visit completed” since there are 2 records in the repeatable table showing one with a 0 and one with a 1 in the followupvisit column. For records 4 and 5, it would say “no follow up visits completed”, since there weren’t any values in the followupvisit column in the repeatable table.. Can anyone assist me with the dax for this? Thanks!calculate total days from a selected date
I have a project currently I am working on right now, and I am struggling to get this methodology to work. For instance, end user selects a date, based on the date selection, it calculates 1. Date diff between the selected date and “Referral date” 2. Calculates the weeks based on the step1. 3. Group step 1 ( so it’s the week falls <10 then group 0-10 week, >=10 && <20 then 10-20 etc) I am particularly struggling around the groups which I am trying to do it in a column as Id need to create another column to sort the Week order and need to show this is a clustered chart. What I really want to do is this. Any help would be appreciated.
