Count distinct families across multiple program fact tables—unique columns break the usual patterns
Hi, first-time poster 👋 Goal How can I count distinct ACT_ID (families) across multiple program fact tables, while letting users slice by shared dimensions (Year, State) and program-specific attributes (e.g. MomsMeet[Location]) At the core we're counting how many distinct ACT_IDs there are, filtered by the columns in the various programs. Background: We’re a non-profit with many programs; each program stores the participants in its own fact table. New columns are added to each program and new programs are added, limits common workarounds (like aggregations). Goal: A model that'll enable counting how many distinct families participate across all of our programs. With slicers to filter only families in some programs, or only those those that equal certain values in columns unique to that program (like MomsMeet[Location]). See image. Simplified model Dimensions • Dim Accounts (ACT_ID, State) this is the family table • Dim Individuals (IND_ID, ACT_ID, Name, Gender) • Dim Programs (Program_ID) • Dim Years (Year) Facts (few examples) • Fact MomsMeet (IND_ID, Year, Program_ID, Location, …) • Fact SummerYouthGroups (IND_ID, Year, Program_ID, Session...) (each fact is unique on IND_ID + Year; largest ≈ 60 k rows, this may help a lot) What I’m after • Modelling pattern that preserves program-specific slicers. • Avoid heavy DAX (like USERELATIONSHIP , CROSSFILTER, TREATAS per slicer) if possible Onedrive .pbix file with synthetic data and program names. Or see model diagram below. Thanks for any pointers! 😀 I'll write in a comment which approaches I tried but didn't work.
Query Resources Issue - Actual vs Budget Finance Report
I am trying to construct a matrix that displays my details as rows, presenting five years of data that includes both actual values and budget figures. Specifically, I have five years of actual data and budget values only for the current year, along with calculations for variances between current year (CY) and last year (LY) actuals, as well as the growth from CY actual to LY actual and budget performance. However, I'm facing a challenge because I lack a dedicated column for these particulars; instead, the data is sourced from various MIS columns across three different tables, with inconsistent naming conventions. I can create the visual representation, but it breaks when I attempt to filter the months using a slicer.
Target achievement based of monthly target and family product
Hello all, Please I am new and I need to create a target achievement in values as well in % for the team that should sum the daily revenues based on the family product name: Below the target as shown based on family based on daily input I need to calculate the reached value/family as well in %. In the below table "Customer Profile" there is the daily input of the sales activity pipeline. I have the account name (Created by email) as well , total monthly fee based on products. In the below table is the target values based on family products I have integrated as below: Thank you!
Suming sales between dates from one table and filters from another
HI PBI community, I have an ask to calcuclate the total sales for stores in an event promotion, and I'm having trouble thinking of an appropriate way to use Calculate since I need values from three tables The first table is a list of stores and the their sales per day like below store_num salestransaction_date sales 3045 4/1/2023 0:00 39909.38 12233 4/2/2023 0:00 27800.62 12000 4/3/2023 0:00 14799.82 33 4/4/2023 0:00 5598.49 8229 4/5/2023 0:00 2113.54 16939 4/6/2024 0:00 2894.86 11267 4/7/2024 0:00 2081.68 16339 4/8/2024 0:00 10574.59 17568 4/9/2024 0:00 6834.87 16810 4/10/2024 0:00 6049.49 The event period information comes from a separate file. I don't necessarily need the "this year or last year" column but I will need to later do YOY sales for each month if that helps. This Year or Last Year Month start end Event Nm TY APR 4/19/2024 4/25/2024 Event C LY APR 4/12/2023 4/15/2023 Event D Finally, there is a seprate list for whether teh store particpated in the event or not based on whether or not they bought a sign kit for the month Month STORE SIGN KIT APR 3045 MINI APR 12233 MINI APR 12000 MINI APR 33 MINI APR 8229 FULL APR 16939 FULL APR 11267 FULL APR 16339 FULL APR 17568 FULL MAY 16810 FULL So I need a connection from the sales transaction date to the promotion dates, and a connection for the Month columns, and a connection for the store_num columns. This makes my data model a little complicated and not sure how I could find sales form that first table between the start and end dates, as well as identifying if they participated. I tried the model below using a date table as a "bridge", but nothing is being filtered - i think there's too many relationships. My deisred result would something like below This Year or Last Year Month start end Event Nm sign kit store count sales TY APR 4/19/2024 4/25/2024 Event C MINI 5 100 TY APR 4/19/2024 4/25/2024 Event C FULL 6 200 LY APR 4/12/2023 4/15/2023 Event D MINI 7 300 LY APR 4/12/2023 4/15/2023 Event D FULL 8 400
Power BI model for Production Process
Hi, I have a model for production process, the problem I have is that I have the fact table with transactional sales have columns of [Item,Category,Date,Quantity,Quantity in bottle,Price per KG) and dimension table of calender table and store table and these dimension table has no duplicate values , but there is another dimension table which is product table (this table has 4 column of [Item,Material,Quantity,Price of Material] t=but this dimension table has duplicate items becasue each item contain many materials so every item is duplicated and some materials are are common between some items also, now I want to create relationship between the product table and sales table to create measure to calculate 1-the total cost of each item 2-total materials used every month in separate visual (the quantity of product in sales table and the quanity of each material in material table) Regards
Help required to implement security via a user information table
I have use case where in user should be able to filter and view data by a security matrix User Information table The report is expected to identify the current logged in user and accordingly filter the data in the report by conditions verified by security matrix and available as in user information Example: Tom being a Team Head should be able to 1. see all Jurisidction applicable when Team - Engineer 2. see only Engineer as the value in Slicer for Team whereas all values for Jurisdiction slicer when blank Slicers are sourced from dimension tables Team & Jurisdiction. I have tried to create measure that captures values individually for team & jurisdiction for the logged in user via USERPRINCIPALNAME() however this works well to filter out the slicers but not on visuals that include Field Parameters aka combination of attributes. User Team = VAR _Team = CALCULATETABLE( VALUES(UserInfo[Team]), UserInfo[User] = "[email protected]") -- to be changed to USERPRINCIPALNAME() RETURN IF( VALUES(Team[Team]) IN _Team,1,0) Welcoming any suggestions on data modelling or approaches or ideas to implement it. Link to sample pbix for reference - Sample PBIX ThanksComposite model view access
Hi, I know there have been a lot of posts on this issue but I can't anything beyond the steps I've already taken for my specific scenario. We have a published custom calendar dataset (1), a published CRM dataset (2) and a published internal sales system dataset (3) The custom calendar dataset (1) is linked to the CRM dataset (2) to create a new dataset (4) That new dataset (4) is combined with the internal sales system dataset (3) and some measures are created in a final composite model (5) That final composite model (5) is used to create a report. That works fine in the desktop app and fine for me to view on the service. All the datasets are published to the same workspace. But another user gets the message that the visualisation can't be displayed as they don't have access to the underlying dataset. It gives them an option to request build permissions on the dataset. The user has read, reshare and build permissions for all 5 datasets (with read and reshare permissions for the actual report) Anyone have any idea what could be the issue?
