Why Do My DAX Formulas Return Wrong Results?
In this session, you will learn how to avoid common pitfalls and mistakes when building calculations using DAX within Power BI. We will explore 10 different DAX calculation examples that return unexpected results and learn how to avoid them, and how to fix them. Through solving these mistakes, you will also learn and brush up on your DAX fundamentals such as Evaluation Context, Iterators, Context Transitions, and Expanded tables concepts.
How does SELECTEDVALUE work?
Hi, I'm trying to create a personal budget report. What I'd like to achieve is to select a month from a slicer and have a matrix visual give me my cash flow for the selected month by account. But what I also want is to see what the cash flow was the previous month and compare the two. The problem is that I use a custom calendar whereby instead of my months starting on the 1st and ending on the 30th or 31st, I tweaked it to coincide with when I get paid. I get paid on the 25th of each month, so I want my months to start on the 25th and end on the 24th of the next month. So I created a table for this. Here is the data: The calendar table: The budget month table: Here are my relationships: Here is my DAX formula for Actual Last Month: But I get no result (see Actual Last Month): current_month variable returns nothing. In the total row, it gives the correct month number. So this makes me think I should probably add the date somewhere to have the date in the row context and not just in the filter context. In the Budget slicer, I used the Budget Name field from the Budget Month table. Is the correct field to select? How can I tweak my DAX or my model to give the correct actual last month based on the selection made in the Budget slicer? Please help.Remove the Row context while keeping Filter context
Hi community, I have a table like the following, where Comp_x are companies belonging to the same group (owner). Table: "Transactions" My goal is to write a DAX measure that calculates internal transactions (ie: transactions between my companies only, excluding third parties), and also the "Eliminations" of such internal transactions, defining an elimination as "the minimum amount between the sum of transactions of Company vs Counterparts in the same Product Line". Therefore, the sum of Elimination column must be always zero. Examples, setting a Filter Context on "Engineering" in the report page, the expected result by Product Line on my visual should be: In the first, the minimum is represented by the sum of Costs (-30) that remains within the same product line (purchases of Comp_2 vs Comp_1), so the amount of Internal Eliminations on Revenues of Comp_1 becomes 30 as well. While setting a Filter Context on "Laboratories" in the report page, the expected result by Product Line on my visual should be: In the second example, the minimum is represented by by the sum of Costs (-55) that remains within the same product line (purchases of Comp_2 vs Comp_1), but in this case Revenues are splitted in 2 kinds. Since there's no link between Revenues of Companies, and Costs of respective Counterparts, the measure should calculate the Elimination starting from the first revenues transactions, until the accumulated limit of 55 is reached. I'm struggling to find a solution due to the presence of Filter Context and Row Context, both coexisting. The logics behind my measures is the following: [Internal Transactions] = "Calculates the Sum of Amount, by P&L Row, where Companies and Counterparts belong to the same filtered Product Line" [Internal Eliminations] = "Calculates the Minimum between the Internal Transactions of each Company vs Counterpart, within she same filtered product line, and assign such minimum value (the limit) to transactions grouped by P&L Row". Hopefully somebody could help me to figure out how to setup the measures. Thank you. Marco Download Example (xlsx)
Currency Switch Measure is Mixing All Currencies at Row Level
Hello, We have a report built where a slicer can select between either US or CA. This is triggering a switch measure to select either the USD$ or CAD$ column from the DB Table: Sales $ = Switch(true(), [OnlyCanadaSelected]="No",[Sales (USD)], [OnlyCanadaSelected]="Yes",[Sales (Local)]) OnlyCanadaSelected = IF(COUNTROWS(FILTER(FlashSalesSites,FlashSalesSites[Country]<> "CA")) = 0,"Yes","No") When trouble shooting the model, I see that "Sales $" column shows local currency for both countries due to the row level context BUT the Grand Total for the matrix is totaling in USD$: I have tried multiple rewrites of the logic but cannot get around the row level. Also, I cannot understand why the Grand Total is correct? I would love some suggestions on how to correct! Thanks
Filtering model on latest version of all attributes from all dimensions through DAX
Hello, I have a model that's comprised of multiple tables containing, for every ID, multiple rows with a valid_from and valid_to dates. This model has one table in that is linked to every other table (a table working as both a fact and a dimension). This fact has bi-directional cross filtering with the other tables. I also have a date dimension that is not linked to any other table. I want to be able to calculate the sum of a column in this table in the following way: - If a date range is selected, I want to get the sum of the latest value per ID from the fact able that is before the max selected date from the date dimension. - If no date is selected, I want to get the sum of the current version of the value per ID. This comes down to selecting the latest value per ID filtered on the dates. Because of the nature of the model (bi-directional with the fact/dimension table), I want to have the latest version of any attribute from any dimension selected in the visual. Here's an data example and the desired outcome: fact/dimension table: ID Valid_from Valid_to Amount SK_DIM1 SK_DIM2 1 01-01-2020 05-12-2021 50 1234 6787 1 05-13-2021 07-31-2021 100 1235 6787 1 08-01-2021 12-25-2021 100 1236 6787 1 12-26-2021 12-31-2021 200 1236 6787 1 01-01-2022 12-31-9999 200 1236 6788 Dimension 1: ID SK Valid_from Valid_to Name 1 1234 10-20-2019 06-01-2021 Name 1 1 1235 06-02-2021 07-31-2021 Name 2 1 1236 08-01-2021 12-31-9999 Name 3 Dimension 2: ID SK Valid_from Valid_to Name 1 6787 10-20-2019 12-31-2021 Name 1 1 6788 01-01-2022 12-31-9999 Name 2 My measure is supposed to do the following: - If no date is selected than the result will be a matrix like the following: Dim 1 Name Dim 2 Name Amount Measure Name 3 Name 2 200 - If July 2021 is selected than the result will be a matrix like the following: Dim 1 Name Dim 2 Name Amount Measure Name 2 Name 1 100 So the idea here is that the measure would filter the fact table on the latest valid value in the selected date range, and then the bi-directional relationships will filter the dimensions to get the corresponding version to that row with the max validity (last valid row) in the selected range date. I have tried to do the following two DAX codes but it's not working: Solution 1: Amount Measure= VAR _maxSelectedDate = MAX(Dates[Dates]) VAR _minSelectedDate = MIN(Dates[Dates]) VAR _maxValidFrom = CALCULATE( MAX(fact[valid_from]), DATESBETWEEN(fact[valid_from], _minSelectedDate, _maxSelectedDate) || DATESBETWEEN(fact[valid_to], _minSelectedDate, _maxSelectedDate) ) RETURN CALCULATE( SUM(fact[Amount]), fact[valid_from] = _maxValidFrom ) Solution 2: Amount Measure= VAR _maxSelectedDate = MAX(Dates[Dates]) VAR _minSelectedDate = MIN(Dates[Dates]) VAR _maxValidFromPerID = SUMMARIZE( FILTER( fact, DATESBETWEEN(fact[valid_from], _minSelectedDate, _maxSelectedDate) || DATESBETWEEN(fact[valid_to], _minSelectedDate, _maxSelectedDate) ), fact[ID], "maxValidFrom", MAX(fact[valid_from]) ) RETURN CALCULATE( SUM(fact[Amount]), TREATAS( _maxValidFromPerID, fact[ID], fact[valid_from] ) ) Now the problem here is that with the first solution, filtering on other dimension work and I get the last version in the selected date range for all attributes of all used dimensions. But the problem here is that the max valid from is not calculated per ID, so I only get the max valid from overall. With the second solution, I do get the right max valid from per ID and the resulting number is correct, but for some reason, when I use other attributes from the dimensions, it duplicates the amount for every version of that attribute. So if somebody can explain this behaviour that will be great, and also, more importantly, if you have any solution to have both the latest value per ID and still keep filtering on other attributes, that would be great! Sorry for the long post, but I thought it's best to give all the details for a complete understanding of my issue, this has been picking my brain since few days now and I'm sure I'm missing something stupid but I turned to this community for help because I cannot seem to be able to find a solution! Thank you very much in advance for any help!Add row/filter context to SUMMARIZECOLUMNS measure
Hi, I have the following measure, which works on a total level, but not in a table visual with fields from another table (dimCampaigns): Unique Opened = CALCULATE ( COUNTROWS ( SUMMARIZECOLUMNS( fctActivities[CONTACT_ID], fctActivities[CAMPAIGNID], fctActivities[SUBJECTLINE], "CountEmailOpen", CALCULATE ( COUNTROWS ( fctActivities ), fctActivities[ACTIVITYTYPE] = "EmailOpen" ) ) ) ) This is probably because SUMMARIZECOLUMNS doesn't have a row context (as stated by this article). So I figured I would have to somehow retrieve such a value from the filter context of the table visual containing data from 'dimCampaigns' into the measure manually (by using VALUES). So I created the following: Unique Opened = CALCULATE ( COUNTROWS ( SUMMARIZECOLUMNS( fctActivities[CONTACT_ID], fctActivities[CAMPAIGNID], fctActivities[SUBJECTLINE], "CountEmailOpen", CALCULATE ( COUNTROWS ( fctActivities ), fctActivities[ACTIVITYTYPE] = "EmailOpen", VALUES(dimCampaigns[CAMPAIGNCATEGORY]), VALUES(dimCampaigns[NAME]) ) ) ) ) This still works on the total level, but still not in the table visual. It's the following table visual where I would like to add the measure to, but if I use the above measure it gives the dreaded error "SummarizeColumns() and AddMissingItems() may not be used in this context.": I think I'm close, but I just cannot get it to work. Any help would be much appreciated, thank you!Average and Sum in one DAX Measures based on row context
Hi All, I am trying to achieve the values in the expected result column where I want to have an average of Values column where metric is headcount but sum of Values where metric on the row is Sales and Profit? Please help, example is given below. Two questions; 1) How can it be achieved it in clculated column? 2) How can it be achieved through DAX meaure using VAR ?Transformed table with new rows
Good day everyone. I need to make transformed table using data from next table: IDLE_ID START_DT START_TIME END_DT END_TIME IDLE_TYPE 122851 09.09.2019 05:57:00 01.10.2019 09:00:00 Planned 123111 01.10.2019 09:30:00 02.10.2019 17:00:00 Planned I need to make visualization like this (abridged example): IDLE_ID START_DT START_TIME END_DT END_TIME IDLE_TYPE 122851 09.09.2019 05:57:00 09.09.2019 24:00:00 Planned 122851 10.09.2019 00:00:00 10.09.2019 24:00:00 Planned 122851 11.09.2019 00:00:00 11.09.2019 24:00:00 Planned 122851 12.09.2019 00:00:00 12.09.2019 24:00:00 Planned 122851 13.09.2019 00:00:00 13.09.2019 24:00:00 Planned 122851 14.09.2019 00:00:00 14.09.2019 24:00:00 Planned 122851 15.09.2019 00:00:00 15.09.2019 24:00:00 Planned 122851 25.09.2019 00:00:00 25.09.2019 24:00:00 Planned 122851 26.09.2019 00:00:00 26.09.2019 24:00:00 Planned 122851 27.09.2019 00:00:00 27.09.2019 24:00:00 Planned 122851 28.09.2019 00:00:00 28.09.2019 24:00:00 Planned 122851 29.09.2019 00:00:00 29.09.2019 24:00:00 Planned 122851 30.09.2019 00:00:00 30.09.2019 24:00:00 Planned 122851 01.10.2019 00:00:00 01.10.2019 09:00:00 Planned 123111 01.10.2019 09:30:00 01.10.2019 24:00:00 Planned 123111 02.10.2019 00:00:00 02.10.2019 17:00:00 Planned I know, it looks really strange, but i need this visualization for easy count of idle for each day. Also first table was formed in DirectQuery, and i need not to change initial table. Any suggestions come useful. Best regards Alexandr
