DAX: Unwanted cartesian product lines?
Please help to calculate/understand properly lastDate and rankDate measures for following simplified example (download) : Desired result: Reality (incorrect subtypes): Why relationship is broken? How to avoid this cartesian product lines? My Measure (I commented workaround, because it's kind of postfilter, not prefilter): rnkDate = VAR t = CALCULATETABLE( VALUES(tstTable[Date]), REMOVEFILTERS(tstTable[Date]) ) RETURN //IF( MAX(tstTable[Amount])<>BLANK(), // WORKAROUND To hide unwantedd rows RANKX( t, LASTDATE(tstTable[Date]) ) //) P.S. Mess happens only if I use fields from dimensional table dimType[Type] (within one table everything is Ok):
Same measure shows different results in different visuals.
Hello community! This is driving me crazy. Here is my measure: Opp YoY = VAR _PriorYR = CALCULATE( SUM('Opportunity Product'[extendedamount] ), Opportunity[createdon].[Year] = MAX (Opportunity[createdon].[Year]) -1 ) VAR _CurrentYR = sum('Opportunity Product'[extendedamount]) RETURN DIVIDE(_CurrentYR-_PriorYR,_PriorYR) When I put this measure into a table, it yields the correct rate of change. When I put this SAME measure into a bar chart for the line, the results ADD 1. It should say 17%, but it says 117%. If I add -1 to the formula, then the table also minuses 1, and I don't want that. I want BOTH to say 17%. What am I missing? EDIT: I figured I should also note that if I remove a 2021 from the bar chart, then the % is correct. It is only when I add another year to the bar chart that it becomes 117% instead of 17%.Get Previous Date and Status with Multiple Groupings in Single Table
I have a table with the following columns: Note that there's a filter to only show one LOA_UID, but the table contains multiple LOA_UIDs. I'm trying to get two additional columns: 1. The Previous Responsibility date (ie, in rows 1-3, I'd expect blank; in rows 4-5, I'd expect 11/7/2022 10:14:36 PM, and so on). 2. The Previous Responsibility (ie, in rows 1-3, I'd expect blank; in rows 4-5, I'd expect "ABC", and so on). This is proving difficult because there's multiple levels of grouping. Each Log_UID is unique across each LOA_UID, and as you can see, there's sorting by CreateDate. So I was unable to figure out the answer from a simple EARLIER command, since the table contains multiple LOA_UIDs. Any help would be appreciated!how to discolor the row values while descending in hierarchy
Hello community, I have a dataset and I tried to color the colomns totals only based on a target column so I create a mesure Detla_N+1 and I disabled the row subtotal and finally I create a color mesure. Color Measure = VAR QTY =new_df[Delta_N+1] RETURN IF(NOT(ISINSCOPE(new_df[New])),(IF(QTY< 0,"Green","Red"))) which gave me the result : till now everything is fine. but I descend in row hierarchy , the other values got colored how do I get rid of coloration ? thanks in advance.
DAX measure in table running out of memory
Hi, I have some DAX measures that are used in a table, they are running out of memory. Is there a better way to do this? I want to display this data in a table. What I want to do is measure the number of days between the latest date in the slicer to the invoice date. I have this measure to do this: DaysSinceInvoice = VAR MaxDate = CALCULATE( MAX('Data DateDimension'[TheDate]), ALLSELECTED('Data DateDimension'[Date])) VAR Result = DATEDIFF(Min([InvoiceDate]), MaxDate, DAY) RETURN CONVERT(Result, INTEGER) Then I have three measures that uses the above measure. 0To30Days = VAR Result = IF(Min('Data InvoiceFact'[Owing]) > 0 && [DaysSinceInvoice] >= 0 && [DaysSinceInvoice] <= 30, Min('Data InvoiceFact'[Owing], 0) Return Result 30To60Days = VAR Result = IF(Min('Data InvoiceFact'[Owing]) > 0 && [DaysSinceInvoice] >= 30 && [DaysSinceInvoice] <= 60, Min('Data InvoiceFact'[Owing], 0) Return Result 60To90Days = VAR Result = IF(Min('Data InvoiceFact'[Owing]) > 0 && [DaysSinceInvoice] >= 60 && [DaysSinceInvoice] <= 90, Min('Data InvoiceFact'[Owing], 0) Return Result This is displayed in a table. Is there a more efficient way to do this? I am using a measure because I want to base the calculation on the date slicer selection.
Group a minimum Datetime by Day and Technician
I am trying to group the minimum arrivedOn datetime by both day and technician using Power BI. Here is an example of my table: I am tasked with finding when a technician arrived on their first job grouped by each day. It seems to me that I would need to first group the minimum arrivedOn datetime by each Technician. And then group that measure by each day. However, everytime I try this it refuses to work. Here is what I am trying to achieve: Any help especially some correct Dax formulas would be very much appreciated. Thanks!KPI Formula Power Pivot
Hello, I am very new in this platform, and I want to learn more. I am currently building a DAX in which I'll be able to produce this formula: Divide(# of Red to Yellow, Red) How do I produce this in DAX. I can't share the data, but I'll try to elaborate the situation further. It's an EWS formula. Red means people will be resigning within 30 days and yellow means within 60 days. It's an RYG tagging wherein we identify people that will be separating from the company. In the formula, the Red should be the count from the minimum selected of date while the yellow is the count of the maximum selected date. So technically the formula should be: # of Employees converted from Red to Yellow / Count of Red from the minimum selected date. It's like Divide(Red to yellow, Red) However, I'm getting a hard time getting the count of employees that were converted from Red to Yellow. Thank you in advance! I'm not sure which dax syntax I should use at this point as I just started using powerpivot/bi's dax this week.Applying Measure after Multiple Selection
Hello All, I have a measure that extrapolates a curve based off the ratio between the latest data point and another line. While it works fine for a single selection, it does not work as intended on multiple selections. I would like the measure to apply/calculate after the selection is done. Hopefully the below pics illustrate my point. Case 1: Vacuum selected. 'Forecast' (orange) extrapolates from latest sales data in relation to the 'High Sales' line. Latest Sale = Oct. 2022. Latest sale = 463. High Sales = 403. Ratio = 463/405 = 1.149. Extrapolation keeps that ratio relative to 'High Sales' curve. Case 2: Broom selected. 'Forecast' (orange) extrapolates from latest sales data in relation to the 'Low Sales' & 'High Sales' line. Case 3: Both Broom and Vacuum selected. 'Forecast' measure does not apply correctly to multiple selection and does not extrapolate from latest sales data. I've linked the DAX measure and sample model below. Please let me know if you have issues accessing it. Link to Sample Model Forecast = // Finds latest sales date VAR EvalDate = CALCULATE ( MAX ( SalesActual[Date] ), ALLSELECTED ( SalesActual ) ) VAR BoundaryDate = EOMONTH ( TODAY (), -2 ) + 1 VAR Date_ = // If latest sales date is older than 2 months, // it will default back to current month. IF ( EvalDate < BoundaryDate, BoundaryDate, EvalDate ) VAR _id = CALCULATE ( MAXX ( ALLSELECTED ( SalesHighLow ), [Product] ) ) VAR SalesHistorical = CALCULATE ( [Actual Sales], 'Calendar'[Date] = Date_, SalesHighLow[Product] = _id ) VAR SalesHigh = CALCULATE ( // Value of sales high curve @ Date_ [High Sales], 'Calendar'[Date] = Date_, SalesHighLow[Product] = _id ) VAR SalesLow = CALCULATE ( // Value of sales low curve @ Date_ [Low Sales], 'Calendar'[Date] = Date_, SalesHighLow[Product] = _id ) VAR _cat = SWITCH ( TRUE (), SalesHistorical <= SalesLow, 1, SalesHistorical <= SalesHigh, 2, SalesHistorical >= SalesHigh, 3 ) VAR _result = IF ( MIN ( 'Calendar'[Date] ) >= Date_, SWITCH ( TRUE (), // TRUE used as first argument allows SWITCH to replace nested IFs Cleaner to read. _cat = 1, CALCULATE ( [Low Sales] * DIVIDE ( SalesHistorical, SalesLow ) ), _cat = 2, CALCULATE ( ( [High Sales] * ( SalesHistorical - SalesLow ) + [Low Sales] * ( SalesHigh - SalesHistorical ) ) / ( SalesHigh - SalesLow ) ), _cat = 3, CALCULATE ( [High Sales] * ( SalesHistorical / SalesHigh ) ) ) ) RETURN _result Thank you!
