IF value exists on calendar date
Hi everyone, i need help creating a measure that evaluates whether a value in on table exists at a particular date within the calender table . I am working with Two tables, on main table that has a customer and a purchase date. The second table is just a table showing the daily calendar dates. the outcome wanted is to evaluate whether a purchase was made by a customer at a certain date on the daily calender. the tables are as shown below : RESULT If a customer has purchased something on the date equivalent to the calendar date, return true, else false. as shown below: If anyone could assist me with writing a measure that could produce this outcome that would be wonderful 🙂 all help and sugegestions are welcome: Thank you
Calculate in measure at column won't filter other objects
Hi! I placed this measure in "values" on a Matrix: measure = CALCULATE(sum(Fact[Totalsum]),Fact[type] = "D") But when i select anything within this matrix it will filter all objects from the model excluding Fact[type] = "D" and showing the absolute total (Measure total is selected) I also tried using SumX but didn't work: measure = SUMX(FILTER(Fact,Fact[type] = "D"),Fact[Totalsum]) Why could this be?
Price Effect - incorrect Sumx calculations when applying several filters from different tables
Hi everyone, I looked on the forum before posting but I coudn't get a resultat that works for my situation. I'd gladly use your help on this because I have been stuck for almost a month now. I'm trying to calculate a price effect on PowerPivot in a measure but the result isn't always correct. When displayed in a Pivot Table with only the products it gives me a result that seem to be correct. The calculation on each product row is correct, and the total is an addition of all my results. It look like this : However, whenever I want to add a filter on the customer (customer category), the total is still correct but not the detail by customer : To explain a bit further how everything is tied together, the data model has 4 tables that look like this : The relations are [Product]Product ID --> Sales[Product ID], [customer]Customer ID --> Sales[Customer_ID], and [calendar]Date --> [Sales]Date. Inside the model I have a few calculations : The first one calculate a turnover without the litigation amount (when there is no cause, there is no litigation with the customer) Gross turnover:=CALCULATE(SUM(Sales[Turnover]);Sales[Litigation cause]="") The seconde one is the same but give the Last Year result : Gross turnover_LY:=CALCULATE([Gross turnover];SAMEPERIODLASTYEAR('Calendar'[Date])) Then, I do exactly the same for the quantity : Quantity_wo_litigation:=CALCULATE(SUM(Sales[Quantity]);Sales[Litigation cause]="") Quantity_wo_litigation_LY:=CALCULATE([Quantity_wo_litigation];SAMEPERIODLASTYEAR('Calendar'[Date])) With those 4 measure, I'm able to compute the € per unit sold : €/Unit_Sold:=[Gross turnover]/[Quantity_wo_litigation] €/Unit_Sold_LY:=[Gross turnover_LY]/[Quantity_wo_litigation_LY] And in the end, here's my calculation that I have a problem with : Price effect:=SUMX(VALUES('Product'[Product_ID]);IF(OR([€/Unit_Sold]=0;[€/Unit_Sold_LY]=0);0;([€/Unit_Sold]-[€/Unit_Sold_LY])*[Quantity_wo_litigation])) I think there is probably something that I still don't really understand about Row context, filter context and context transition, That prevents me from achieving what I want ... I have to add that in the sales table, an article can be sold at 2 different customers for a different price. Thus I'm even starting to doubt that VALUES(VALUES('Product'[Product_ID])) even gives me a correct answer when I don't apply a filter on customer. Here is the link to the excel file if you want to take a closer look : Price_effect Thank you very muchfunction Values doesn t return Table of values but error
Hi, I want to use VALUES function and study from https://dax.guide/values/ that input is table/column and output table (one or more columns) I tried to od it on most simplest case but it failed, can sombebody told me why ? everything seems OK based on DAX guide, what I missed ? Current Data Model My DAX formula Measure = VALUES(Locations[Location]) When I tried to test it on table Measure = VALUES(Locations) I got another error "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value." I am totally confused, because there is only one condition that input parameter must be table or column, any ideas what I missed ? (Don t think so that DAX.guide is wrong in description) thanks in advance!
PBI Report Builder - removing duplicate values in parameter dropdown
I'm currently working through building a paginated report with PBI Report Builder, but a small issue came up. One of my parameters is called Spend Year. Currently, for the sake of working with a faster loading file, I filtered this down to only include the year 2023. However, the issue with this is that the parameter dropdown is displaying multiple options for 2023. I believe it's displaying a 2023 for each row in the dataset that contains one. Having multiple options for the same thing just isn't practical, so I've been looking for a way to have the parameter dropdown display distinct options. I managed to find this article: https://www.c-sharpcorner.com/article/remove-duplicate-filter-values-from-ssrs-parameter-drop-down/. While this matches my situation, the VB code that he writes is for String values. I'm working with integers. I tried my best to adapt his code to work for integers (you can find the code below), but after following through everything, my Spend Year parameter is now greyed out with no selectable values. I was sure to configure the available values as detailed in the article. Does anyone happen to have any idea how I should go about this? If it's useful to know, my data was pulled via a DAX query. Thank you in advance! My version of the code: Public Shared Function RemoveDuplicates(parameter As Parameter) As Integer() Dim items As Integer() = parameter.Value Array.Sort(items) Dim k As Integer = 0 For i As Integer = 0 To items.Length - 1 If i > 0 AndAlso items(i) = items(i - 1) Then Continue For End If items(k) = items(i) k += 1 Next Dim unique As Integer() = New Integer(k - 1) {} Array.Copy(items, 0, unique, 0, k) Return unique End Function
Count Distinct but ignore any values counted in prior months
Hi, Im trying to calculate a distinct count of exporters and display this on a column chart with the snapshot month on the x axis. I want to exclude the exporters already counted in previous months so that each column will add up to the total of unique exporters so far this year of 178. my code seems to have a distinct count per month but does not exclude any values counted in prior months.. what is wrong with my approach here? VAR CurrentMonthOrder = selectedvalue('Supported Business'[RR SUP BUS MONTH ORDER]) VAR PreviousMonths = calculatetable( values('Supported Business'[Exporter Name]), 'Supported Business'[RR_SUPPORTED_FY]="2023-2024" && 'Supported Business'[RR SUP BUS MONTH ORDER] < CurrentMonthOrder ) VAR ExportersThisMonth = calculatetable( values('Supported Business'[Exporter Name]), 'Supported Business'[RR_SUPPORTED_FY]="2023-2024" && 'Supported Business'[RR SUP BUS MONTH ORDER] = CurrentMonthOrder ) /* VAR UniqueExportersThisMonth = EXCEPT( ExportersThisMonth, PreviousMonths ) */ var test = calculate(DISTINCTCOUNT('Supported Business'[Exporter Name]), 'Supported Business'[RR_SUPPORTED_FY]="2023-2024", not('Supported Business'[Exporter Name]) in PreviousMonths ) RETURN test
Issues with level of detail calculation using a relationship model
I have 2 tables related. Table A (Maestro de partes) is the dimensional table, where each SKU code (NOPARTE) and brand group (CodMarca) is located. Table B (Liq 3M) is the fact table, stores the transacction at the SKU and date level from each import process in the last 3 months. I need to calculate the sum of the import costs over the FOB factory price at the brand group level. I also need this value to remain the same whether I disaggregate it per SKU or brand group. Here is the formula I'm using considering the filters I need for this calculation: Factor de importacion = CALCULATE( ([Total costos de importacion]/SUM('Liq 3M'[FOB unit liquid]) + 1), FILTER( 'Liq 3M', 'Liq 3M'[Via transporte] = 2 && 'Liq 3M'[Datediff aplicacion] > 0 && 'Liq 3M'[Datediff aplicacion] < 4 ), ALLEXCEPT( 'Maestro Partes', 'Maestro Partes'[CodMarca] ) ) https://1drv.ms/u/s!Ar7k80093sB_gckbSsUJKl9osviTWw?e=VfMKnh This is what I get: This is what I need: Any help will be appreciated!   Regarding using VALUES as a filter argument in CALCULATE
Hi all, I had learned that writing a measure of AllSalesCurrentYear, especially when computing a percentage over the grand total of only the current year, shall use VALUES('Date'[Year]) as a filter argument in CALCULATE. The V1 code as follows, AllSalesCurrentYear V1 = CALCULATE( [Sales Amount], ALL('Sales'), VALUES('Date'[Year]) ) I know that VALUES only return the list of values of a column in the current filter context. But for me, it seems a bit weird to only use VALUES('Date'[Year]) as a filter argument. The reason is that the output of VALUES('Date'[Year]) is a unique list like the photo below, I thought the code shall change to 'Date'[Year] IN VALUES('Date'[Year]) that makes more sense for me. And then after trying the code, I found those result are the same. The V2 code is as follows, AllSalesCurrentYear V2 = CALCULATE( [Sales Amount], ALL('Sales'), 'Date'[Year] IN VALUES('Date'[Year]) ) The matrix is as follows, Does anyone has a better understanding of the V1 code? How to explain for people that can only use VALUES('Date'[Year]) as a filter argument for this case. Thanks!
