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.
Incorrect result when using multiple similar measures with TREATAS and variables in the same visual.
When two measures that have a very similar looking but different calculation both use TREATAS with a variable as the first TREATAS parameter and are used in the same visual the output of one of the measures will incorrectly be a duplicate of the output of the other measure. This looks like an incorrectly applied DAX fusion optimization, which measure output gets duplicated seem to depend on which measure you add to the visual first. This is what the output should look like: Here is the incorrect output: Here is the DAX code of the two measures: Here is the data from the simple 2 table data model used to demonstrate the problem. I can provide a PBIX file that demonstrates the problem if needed.
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
Difficulties relating data across multiple tables
I am making a productivity dashboard. The team is comprised of a set group of individuals and a group of float/PRN folks that help fill gaps. To identify when a float member worked, there is a spreadsheet on SharePoint that the manager fills out with the teammates name and date they worked. Because a teammate can work multiple days, I created a bridge table that contains the distinct names from that document to join it to my main query. The link is their name as that is attached to the work they do (ignor the errors in the main table; that is where I was trying to break this down into steps and failed). The piece that falls apart for me is when I try to relate the 'Float Team' table to the 'PICS Data' table. Power BI tells me there is no relationship between them. I've looked through the data to make sure those teammates exist in the main query and they do. Here is the latest version of the formula I tried. I'm getting and error on the line 'PICS Data'[Verified_Date] = RELATED('Float Team'[Worked_Date]) stating "Parameter is not the correct type". If I remove RELATED (because it is the many side of a relationship), I get "Cannot find name [Worked_Date]". PICS_Filter_3 = IF ('PICS Data'[Verifying_Teammate_Name] IN VALUES ('Float Team'[Epic_Name]) && CALCULATE( COUNTROWS('PICS Data'), USERELATIONSHIP('Float Team Bridge'[Epic_Name], 'PICS Data'[Verifying_Teammate_Name]), USERELATIONSHIP('Float Team Bridge'[Epic_Name], 'Float Team'[Epic_Name]), 'PICS Data'[Verified_Date] = RELATED('Float Team'[Worked_Date]) ), "PICS", "SITE")
Shortages calculation
Hi PBI Community Members, I need help building measure logic to calculate shortages and below is the logic and scenario. Core tables ---> Products (Dim) ---> Order (Fact) ---> Stocks (Fact) ---> BOM (Dim) First of all, I have one Dimension which is Products containing unique columns (in single column ) for finished and semi-finished products. Then I have orders where I have an Order for finished products. Orders have * to 1 relationship with Products. I have one more table for Stocks where we have stocks for all Products including finished and semi-finished products. Stocks have * to 1 relationship with Products. Then I created one measure to calculate Required Production based on [Orders] - [Stocks] I have another table where I have Bill of Material (BOM) information, have one column for Finished Product and second column for it's components (semi finished product) like for Finished Product = A1 ---> Semi Finished Product = B1, B2, B3 in short in order to produce A1 I need those 3 component and for that I need to calculate the Stocks first and then each component needs. Products 1 to * BOM (Inactive relationship) & Products 1 to * BOM (Inactive relationship) Please feel free to ask me if mor einformation required. Thanks in advance!Handling multiple Fact Tables with different Aggregation
Dear Community, I have 2 dim and 2 fact tables with the following model: So the problem I get is in vizulising both fact values in one Visual without grouping (don't want to MAX or SUM the BookingDate or costs). Here the Name "abc" should get 30 sales and three different costs and the other Names ("def" and "ghi") should get 30 and 15 sales and no costs (tables on the right). But as you can see the Visual showing all the values of both fact tables without any relationship (table in the middle): I would expect this: Does somebody has any Idea? Thanks in advance!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 advanceNeed help to use inactive and active relationship simultaneously in dax
Hello, I am trying to use active and inactive relationship based on the Primary Key in a single measure to use it on a single visulaization. Scenario: If Key is found in Table C via active realtionship use active relationship in measure to find out sales. If key is not found via active realtionship then compare Table A key with Table C using inactive relationship Tried below measure but this doesnt seem to be working userrelationship = if(SELECTEDVALUE('Table B'[Key])=SELECTEDVALUE('Table c'[Key]),[salesnew], CALCULATE([salesnew],USERELATIONSHIP('Table C'[Key],'Table A'[Key]))) Thanks
