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!
Paginated Reports - Parameters
Hi everyone! When I run the report to see rendered reports, as you see in the screenshot, only the Category and SubCategory parameters are enabled to write/select and I created all parameters in the same way but the Item key and Item no are not writable fields, what should I do to make them writable as default too? Thank you very much for the answers in advance!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.
