Forum Widgets
Recent Discussions
Power BI DAX Challenge – ALLSELECTED vs Correct Grand Total
I have a fact table with sales document and other columns. For reporting purposes, I need to display the total Sales Order Weight at the Sales Document level, so I use ALLSELECTED() to ignore the other row context while still respecting report slicers. Current requirement Suppose the raw data is: Sales Document Item Bill of Lading Weight SO1 10 BOL1 600 SO1 20 BOL2 594 I need my report to show: Sales Document Bill of Lading Sales Order Weight SO1 BOL1 1194 SO1 BOL2 1194 The repeated value is intentional. Slicer requirement If the user filters Classification, Material, Delivery, Plant, etc., the measure must respect those slicers. Example: If only Item 10 remains after a slicer, the measure should return 600, not 1194. This is why I'm using ALLSELECTED() instead of REMOVEFILTERS(). The problem The row values are correct, but the Grand Total is incorrect because the repeated Sales Document weight gets summed multiple times. The expected total is: 1194 but Power BI returns: 1194 + 1194 = 2388 (or similar duplication depending on the number of Bill of Lading rows). Current measure (simplified) CALCULATE( SUM('Sales&Delivery'[Sales Order Weight of the Item in Grams]), 'Sales&Delivery'[Delivery] <> BLANK(),ALLSELECTED('Sales&Delivery'[BillOfLadingDrvd]), ALLSELECTED('Sales&Delivery'[Delivery]), ALLSELECTED('Sales&Delivery'[Default Plant]), ALLSELECTED('Sales&Delivery'[PlantDelivery]), ALLSELECTED('Sales&Delivery'[ActualGoodsMovementDate]), ALLSELECTED('Sales&Delivery'[Classification]) ) Things I've already tried SUMX(VALUES(SalesDocument), ...) SUMMARIZE() ALLEXCEPT() KEEPFILTERS() TREATAS() Calculated column approach Visual Calculations None of them satisfy all three requirements simultaneously. Question How can I write a DAX measure that: Repeats the Sales Order Weight for every belonging to the same Sales Document. Respects all report slicers (Material, Classification, Delivery, Plant, etc.). Produces a Grand Total that counts each Sales Document only once instead of summing the repeated values. Is there a DAX pattern for this?
Help with DAX
Dear Pros, Need help here I have a monthly recurring charge (MRC) from multiple customers that continues every month until the end of their contracts. I want to calculate the MRC amount for each month. For example, Customer A starts billing in January and pays $100 every month until the contract ends, while Customer B starts billing in March and pays $50 every month until the end of the contract. So I want see how much money we have received every month and from which customers. Something like this Jan - 100 Feb - 100 Mar - 100+50 Apr - 100+50 .... so on Note : I don't have the end of the MRC month date but I have the start of the MRC billing and number of billable months in each year please help with dax
Comparaison de courbes en S par jalon et par quarter — Quelle architecture DAX ?
Bonjour à tous, Je travaille sur un rapport Power BI de suivi de production et j'aimerais avoir vos conseils sur l'architecture à adopter. Contexte Je suis en train de construire des courbes en S cumulatives qui mesurent, jour après jour, le pourcentage de SKU-SITE ayant atteint un jalon de production donné. Disons que les jalons suivis sont : Jalon1, Jalon2, Jalon3. Les données sont organisées par quarters : 24Q1, 25Q1, 26Q1, etc. Mon modèle de données est le suivant : Dim_SKU (one) → Fact_SKU-SITE (many) par le code SKU (Dim_SKU stock les infos de mes skus, la fact sku-site m'apporte une combinaison entre un sku et site de production (pk) car un sku peut se retrouver dans plusieurs site de production) Une table disconnected Axe X Courbes S utilisée comme axe X, reliée à Fact_SKU-SITE via des relations inactives activées par USERELATIONSHIP. La timeline se calcule comme cela : Date Event (un évènement important pour le début de la prod) -> JalonX (en jours), chaque timeline a donc une relation inactive avec ma table X courbes S [value] Ce que je veux obtenir Afficher sur un même graphique en courbes, plusieurs séries superposées correspondant à des combinaisons librement choisies, par exemple : Jalon1 26Q1, Jalon1 25Q1, Jalon2 26Q1, etc. L'idéal serait de pouvoir sélectionner les combinaisons à afficher via des slicers. Ce que j'ai déjà Une mesure fonctionnelle par combinaison jalon × quarter, construite ainsi : % Courbe S Event > Jalon1 26Q1 = VAR Jour = SELECTEDVALUE('Axe X Courbes S'[Value]) VAR Numerateur = CALCULATE( DISTINCTCOUNT('Fact_SKU-SITE'[PK_SKU_x_Site]), USERELATIONSHIP('Axe X Courbes S'[Value], 'Fact_SKU-SITE'[Event > Jalon1 (jrs)]), FILTER(ALL('Axe X Courbes S'), 'Axe X Courbes S'[Value] <= Jour), 'Fact_SKU-SITE'[Jalon1 Statut A/B] = "A", NOT(ISBLANK('Fact_SKU-SITE'[Event > Jalon1 (jrs)])), KEEPFILTERS(FILTER(ALL('Dim_SKU'), 'Dim_SKU'[SKU Quarter (pour filtre)] = "26Q1")) ) VAR Denominateur = CALCULATE( [NB SKU-SITE], KEEPFILTERS(FILTER(ALL('Dim_SKU'), 'Dim_SKU'[SKU Quarter (pour filtre)] = "26Q1")) ) RETURN DIVIDE(Numerateur, Denominateur) Le problème Pour afficher N courbes superposées sur un même graphique, Power BI nécessite N mesures distinctes dans le puits Valeurs. Avec 3 jalons × 4 quarters, cela représente déjà 12 mesures. Le nombre va croître avec les quarters. L'IA m'a envisagé une architecture avec : 1 mesure métier par jalon contenant toute la logique 1 wrapper one-liner par combinaison appelant la mesure métier avec le quarter forcé Mais je me demande s'il existe une approche plus élégante, notamment via Field Parameters, Calculation Groups, ou toute autre technique qui permettrait de réduire le nombre de mesures tout en conservant la possibilité d'afficher plusieurs courbes superposées de différent jalons et différent quarter sur un même graphique. Merci d'avance pour vos retours ! Désolé pour la longueur du post, mais j'ai préféré ajouter plus de contexte que pas assez
Bug: Base64 Text Measures break down when having Calculation Groups
Hi all. I have a few concatenate measures to bypass the 32000-character limit in Power BI - They all work fine until I add a calculation group and calculation item - they all break down then. You don't even have to use it - the mere existence of a calculation item is enough to break them. I cannot find much info on the web about this very niche issue. Hopefully, someone has an answer/explanation. Cheers, QuangHow to compare static table to updated table, find rows that changed?
At the start of each month, I take a snapshot (TABLE1) of a live data table (TABLE2). TABLE2 has three relevant columns that defines a unique row (Title, Amount, Salesperson), and a fourth column that can change (Date): Title Amount Salesperson Date The snapshot also adds a "Snapshot date" column. it's relevant headers look like: Title Amount Salesperson Date Snapshot Date There's a table visual to display these snapshot items and a Year, Month slicer (Relationship: CalenderTable[Date] -> TABLE1[Snapshot Date]) to filter the snapshots to only a specific month. I now want to compare these snapshot items to the live data table and have the table visual only show rows where the Date column changed to a later date. After some googling, I created a Measure I can use to filter the table visual to values of "1", but the date comparison doesn't seem to be working right. It's returning some rows where the Date in TABLE2 was changed to earlier (we don't care about those) than the Date in TABLE1 and I can't figure out why. Rolled = If( COUNTROWS( CALCULATETABLE(TABLE2 , TABLE2[Name] = SELECTEDVALUE(TABLE1[Name]) && TABLE2[Amount] = SELECTEDVALUE(TABLE1[Amount]) && TABLE2[Salesperson] = SELECTEDVALUE(TABLE1[Salesperson]) && TABLE2[Date] > SELECTEDVALUE(CalendarTable[Date]) ) ) > 0, 1, 0 ) Example of wrongly listed TABLE1 row: TABLE2 is not connected by any relationships. I manually applied filters to show the same row to see if the measure worked properly. What am I doing wrong or is there a better way to do this?
SUMX Not Working
I have created a sample dashboard based on the AdventureWorks sample database and I cannot figure out why I cannot get this to work. I can evaluate every VAR throughout except _Result which never works. I have attached a photo of _FilteredTable, which you can see is a very simple table with a total. (the code snippet is showing up incorrectly, so I've also included a screenshot of the fairly simple code) DEFINE VAR _TableWithBadNames = SUMMARIZECOLUMNS ( 'Production ProductCategory'[Name], 'Sales SalesOrderHeader'[OrderDate].[Year], "@Total", SUM('Sales SalesOrderDetail'[LineTotal]) ) VAR _Table = SELECTCOLUMNS( _TableWithBadNames, "@Category", [Name], "@Year", [Year], [@Total] ) VAR _FilteredTable = FILTER(_Table, [@Category]="Accessories" && [@Year]=2011) VAR _Result = SUMX(_FilteredTable, [@Total]) EVALUATE _ResultMeasure for drilling down on a pie chart with categorical data, excluding blank cells
Hello all. Here is some dummy data for what I am trying to accomplish: Respondent Alumni Alumni Level Alumni Undergrad Program 1 Yes Undergraduate Elementary Education 2 No 3 Yes Graduate 4 Yes No 5 Yes Undergraduate Middle School Education 6 Yes Graduate 7 No Of course I have unpivoted by Respondent (which is not shown here). What I would like to do is have a pie chart showing "Alumni" and then be able to drill-down on the percentage that have answered "Yes" so I can see the percentage for "Alumni Level" and then even further drill down to the percentage for "Alumni Undergrad Program." Of course, I would need to disregard the respondents that answered "No" for the "Alumni" question. Thanks so much!
