I have been trying to make a new table and want to use the date of the existing data. Can anyone help me understanding what is wrong with this formula?

dDate = CALENDAR(DATE(YEAR(MIN(Ftransactions[Date])),1,1),DATE(YEAR(MAX(Ftransactions[Date])),12,31))

The error which keeps popping up after pressing enter is: > An argument of function 'YEAR' has the wrong data type or the result is too large or too small. If the argument is expected to be a date, that date must be between January 1, 100 and December 31, 9999.

I am struggling with a DAX measure. Here is the fact and 2 dimension tables I have: factAction(dimAccountID, dimActionID, Date, ActionQty) dimAction(dimActionID, ActionCode, ActionDescription) dimDate(Date, WorkingDay) The relationships are: dimDate.Date -> factAction.Date dimAction.dimActionID->factAction.dimActionID And I have a DAX measure for ActionQty = SUM('factAction'[ActionQty]) We want to report on the number of actions of ActionCode AAA. Fine, that's easy. Just select ActionCode = AAA into the Power BI filter. Now we want to also report, for those Accounts that had ActionCode AAA, the sum of ActionQty for ActionCode AAB where the ActionDate is within 2 working days of the AAA Action Date. Any suggestions would be welcome! Edit: added sample data and expected output | AccountID | Date | ActionCode | ActionQty | |---|---|---|---| | AC1 | 06-Apr-20 | AAA | 1 | | AC1 | 09-Apr-20 | AAB | 1 | | AC1 | 07-Apr-20 | BBB | 1 | | AC2 | 16-Apr-20 | AAA | 1 | | AC2 | 20-Apr-20 | AAB | 1 | So when Filtering for AccountID AC1 and April 2020, this new measure would return 0 (because the number of working days between the AAA and AAB ActionCodes for this AccountID is greater than 2) When filtering for AccountID AC2 and Aptil 2020, the new measure should return 1 (because the number of working days between 16 April and 20 April is less than 2)

I am learning DAX and reading Russo & Ferrari's Definitive Guide to Dax (2nd ed). I understand how SUM ( Sales[Quantity] ) is really SUMX ( Sales, Sales[Quantity] ), which iterates over the rows of the Sales table and sums the values in the Quantity column. If I'm not mistaken, even though the book says it's against the standard formatting (so its clear the column is not a measure), in SUMX you could remove the table name Sales from the argument Sales[Quantity], since SUMX already knows to look in Sales. I took some issue with the syntax of how SUMX (and other iterators) is combined with FILTER, based around the table name being used in the column reference. On page 58 an example code is given:

Sales Amount Multiple Items :=
VAR
    MultipleItemSales = FILTER ( Sales, Sales[Quantity] > 1 )
RETURN
    SUMX (
        MultipleItemSales,
        Sales[Quantity] * Sales[Unit Price]
    )

My initial understanding was that MultipleItemSales is an entirely new object in memory, which is a filtered version of the Sales table - therefore I thought the second argument should be MultipleItemSales[Quantity] * MultipleItemSales[Unit Price], since SUMX is iterating over MultipleItemSales and not Sales. But apparently this would give an error. I think the question becomes *what is DAX doing internally to make this syntax valid?* Let me give another example:

VAR
    MultipleItemSales = FILTER ( Sales, Sales[Quantity] > 1 )
VAR
    SingleItemSales = FILTER ( Sales, Sales[Quantity] = 1 )
VAR
    SalesAmountMultipleItems =
        SUMX (
            MultipleItemSales,
            Sales[Quantity] * Sales[Unit Price]
        )
VAR
    SalesAmountAllItems =
        SUMX (
            Sales,
            Sales[Quantity] * Sales[Unit Price]
        )
VAR
    SalesAmountSingleItems =
        SUMX (
            SingleItemSales,
            Sales[Quantity] * Sales[Unit Price]
        )
VAR
    AvgSalesMultipleItems =
        AVERAGEX (
            MultipleItemSales,
            Sales[Quantity] * Sales[Unit Price]
        )
RETURN
    ....

In each of the SUMX functions, a different version of the table (filtered or unfiltered) is being iterated, yet Sales is always used as the table name for the columns. So, how does it know which version of Sales to use? I then thought, rather than creating an entirely new object in memory, FILTER might actually filter the object which Sales points to in-place. So in SalesAmountMultipleItems, the first argument to SUMX tells the system to apply some filtering to Sales, before the looking down the Sales[Quantity] and Sales[Unit Price] columns. Then, when escaping the SUMX function scope, whatever filtering done to Sales gets undone, allowing SalesAmountAllItems to access the unfiltered version Sales. But then I learned about lazy evaluation. In my previous argument, when evaluating AvgSalesMultipleItems, the filtering applied by MultipleItemSales would have to be applied a second time. However, to save CPU and memory, it's my understanding that the filtering wouldn't need to happen a second time if it's already been carried out once. So, my current understanding is more like the follwing: FILTER does indeed create a new object in memory - so MultipleItemSales is a filtered version of Sales which can be referenced multiple times but need only be created once. But then to allow the continued use of the name Sales, the effect of the first argument of SUMX being MultipleItemSales must be to **temporarily change which object in memory the name Sales is pointing at**. At the end of the scope, the name Sales reverts back to pointing to the original, unfiltered version of the table, allowing SalesAmountAllItems to access it. Is this the correct understanding?

Below is my sample table and I'd like to write a Dax statement that returns the current count whenever I filter for Team A or B. Ideally, the current count per team would be on a card in power BI. | team | yearmonth | active_cnt | |-------:|:----------|------------:| | a | 2023-06 | 10 | | a | 2023-07 | 15 | | a | 2023-08 | 30 | | b | 2023-06 | 15 | | b | 2023-07 | 25 | | b | 2023-08 | 30 | Below was my attempt but it returned a blank response: CurrentCountPerTeam = SUMX( FILTER( 'Query1', 'Query1'[yearmonth] = YEAR(TODAY()) * 100 + MONTH(TODAY()) ), 'Query1'[active_cnt] )

I can't manage in **DAX**, to calculate for a year, the sum of the values which are in this year, I would like the proportion of each of the values in a year according to its starting date and its ending date. For example, I want to be able to calculate for each year, the value of the "workload" during that year, so in 2019 the share of 150 between 01/02/2019 and the end of the year then the share of 150 between 01/01/2020 and 12/31/2020 then the share between 01/01/2021 and 05/05/2021 (without forgetting the phase 1-2 and the other IDs obviously).

I have a DAX variable that contains a table. How can I reference a particular column in that variable? For example, in the below command, the

returns an error. But it works if I replace

with

(which is the name of the table which contains that column)

define var table1=FactResellerSales
EVALUATE ROW("a",COUNTBLANK(table1[SalesAmount]))

How can I convert this SQL statement to DAX? SQL query and tables: select SUM(Kiekis ) from Info left outer join Prekes on Info.PrekeID = Prekes.PrekeID left outer join PrekiuGrupes on Prekes.PrekiuGrupeID = PrekiuGrupes.PrekiuGrupeID group by PrekiuGrupesID

Sample data: -------------------------------------------------- | Customer | Value | -------------------------------------------------- | A | 5 | -------------------------------------------------- | B | 10 | -------------------------------------------------- | B | -10 | -------------------------------------------------- | C | 10 | -------------------------------------------------- | C | 5 | -------------------------------------------------- What I want to do is to count the distinct number of customers where the sum of values is not 0. Currently I'm using DISTINCTCOUNT(Customer) which provides 3 (A,B,C) whereas the intended output is 2 (A,C). Edit: As this is tagged both Power BI and DAX, I want to stress that I'm looking for DAX solutions, not SQL. As such, I've reverted the change to the table as I don't believe the SQL expression shows my data as accurately.

I am stumped. I am calculating an average of products sold per location. Then, I am using AVERAGEX to calculate that average for each sales associate. But how do I find the MAX of that average and use that as a benchmark for each associate? I want to know for each associate the difference between the MAX AVERAGE products/location and the associates AVERAGE products/location. I've wanted to use some sort of summary table, but this is a problem because then I lose the contexts I need (dates, locations with sales in that date period). Below are the measures I have so far: ProductsPerLocation = DIVIDE([TotalSolutions],[TotalLocations]) AveProductsPerLocationByAssociate = AVERAGEX(VALUES(Employees[EmpID]),[ProductsPerLocation]) Hopefully this makes sense. Thanks!

I want to record measures from a Tabular model, as KPIs, and store the values in a table in SQL Server. I have created a linked server from my SQL Server instance to my SSAS instance. I have written a stored procedure to execute the DAX code via OPENQUERY, with the intention of storing these in a temp table, before loading them in to the KPI table. I am using a temp table because I am querying multiple tabular models. My problem occurs when I try to update my temp table with values from my OPENQUERY output. My OPENQUERY output is currently within a CTE, and I was hoping to do a simple join to the temp table, but because the output from the DAX query returns each column name within [ ], when I try to join on one of the OPENQUERY columns I receive the error "Invalid column name...". E.g. UPDATE temp SET temp.[Current Contract Count] = cte.[Contract Count] FROM #ServiceZoneKPIs AS temp INNER JOIN tabular_cte AS cte ON cte.[Copy of Service Zone Code] = temp.[ServiceZoneAlternateKey] The error occurs because 'Copy of Service zone' does not exist in the OPENQEURY output; the output column name is [Copy of Service Zone]. I may well be missing a simple trick here? How can I join an OPENQUERY output, returning tabular model data, to my T-SQL temp table?

I am trying to convert the following SQL row level security function into a DAX filter within a Tabular model CREATE FUNCTION [Security].[fn_securitypredicate](@BrandID AS INT, @ChannelId AS INT) RETURNS TABLE WITH SCHEMABINDING AS RETURN (SELECT 1 AS fn_securitypredicate WHERE (EXISTS ( SELECT 1 FROM security.RLSStaffBrand WHERE StaffUsername = SYSTEM_USER AND BrandId = @BrandID) AND EXISTS ( SELECT 1 FROM security.RLSStaffChannel WHERE StaffUsername = SYSTEM_USER AND ChannelId = @ChannelID) ) OR ( EXISTS (SELECT 1 FROM security.RLSStaffBrand WHERE StaffUsername = SYSTEM_USER AND BrandId = @BrandID) AND NOT EXISTS ( SELECT 1 FROM security.RLSStaffChannel WHERE StaffUsername = SYSTEM_USER ) )-- this user is not restricted by Channel OR (NOT EXISTS ( SELECT 1 FROM security.RLSStaffBrand WHERE StaffUsername = SYSTEM_USER) AND EXISTS ( SELECT 1 FROM security.RLSStaffChannel WHERE StaffUsername = SYSTEM_USER AND ChannelId = @ChannelID) ) ) GO So far I have the following DAX filters, but this only handles the first condition in the SQL code. I don't know if it's even possible to replicate the rest in DAX. ='Brand'[BrandId]=LOOKUPVALUE('RLSStaffBrand'[BrandId], 'RLSStaffBrand'[StaffUsername], USERNAME(), 'RLSStaffBrand'[BrandId], 'Brand'[BrandId]) ='Channel'[ChannelId]=LOOKUPVALUE('RLSStaffChannel'[ChannelId], 'RLSStaffChannel'[StaffUsername], USERNAME(), 'RLSStaffChannel'[ChannelId], 'Channel'[ChannelId])

I am going to make a KPI table, however I've yet to decided where to make it. In essence its going to show weekly numbers derived from our customer database. *Weeknumber *New users (users with startdate within that week) *Total users (users with startdate before that week, and enddate after that week/NULL) This is going to be expanded (New users pr sales channel for example). The result is that users should be able to compare these numbers week for week. I do not know if this is possible to make in DAX (Using dax, can I filter week by week, sales channel or other dimensional traits?) or if I should do this in sql.

I'm having a hard time to understand how to filter out result of multidimensional expression. This is my database schema. And this is my data. Manufacturer ╔════╦═══════════════════╗ ║ Id ║ Name ║ ╠════╬═══════════════════╣ ║ 1 ║ Awesome Computers ║ ║ 2 ║ TailSpin Toys ║ ╚════╩═══════════════════╝ Item ╔════╦═════════╦════════════════╦═══════╗ ║ Id ║ Name ║ ManufacturerId ║ Stock ║ ╠════╬═════════╬════════════════╬═══════╣ ║ 1 ║ PC ║ 1 ║ 40 ║ ║ 2 ║ Server ║ 1 ║ 10 ║ ║ 3 ║ STB ║ 2 ║ 80 ║ ║ 4 ║ Console ║ 2 ║ 50 ║ ╚════╩═════════╩════════════════╩═══════╝ Part ╔════╦══════════════════╦════════╦══════════╦═══════╗ ║ Id ║ Name ║ ItemId ║ StatusId ║ Stock ║ ╠════╬══════════════════╬════════╬══════════╬═══════╣ ║ 1 ║ MBO ║ 1 ║ 1 ║ 100 ║ ║ 2 ║ Processor ║ 1 ║ 1 ║ 100 ║ ║ 3 ║ Server MBO ║ 2 ║ 2 ║ 20 ║ ║ 4 ║ Server processor ║ 2 ║ 2 ║ 20 ║ ║ 5 ║ Main box ║ 3 ║ 2 ║ 40 ║ ║ 7 ║ Adapter ║ 3 ║ 3 ║ 30 ║ ║ 8 ║ Controller ║ 4 ║ 2 ║ 40 ║ ║ 10 ║ Adapter ║ 4 ║ 1 ║ 60 ║ ║ 11 ║ Memory card ║ 4 ║ 2 ║ 80 ║ ╚════╩══════════════════╩════════╩══════════╩═══════╝ Status ╔════╦═════════════╗ ║ Id ║ Name ║ ╠════╬═════════════╣ ║ 1 ║ No data ║ ║ 2 ║ Available ║ ║ 3 ║ Unavailable ║ ╚════╩═════════════╝ I imported everything into the tabular model solution. After this, I created two measures: - Table Item: **ItemStock:=SUM([Stock])** - Table Part: **PartStock:=SUM([Stock])** Then I deployed the cube to the server. By running the following MDX query... SELECT NON EMPTY { [Part].[Name].CHILDREN } ON ROWS, { [Measures].[PartStock] } ON COLUMNS FROM [Model] WHERE ( { [Status].[Id].& } ) ...I get this resultset... ╔═══════════╦═══════════╗ ║ ║ PartStock ║ ╠═══════════╬═══════════╣ ║ Adapter ║ 60 ║ ║ MBO ║ 100 ║ ║ Processor ║ 100 ║ ╚═══════════╩═══════════╝ ...which is ok. However, when running this MDX query... SELECT NON EMPTY { [Item].[Name].CHILDREN } ON ROWS, { [Measures].[ItemStock] } ON COLUMNS FROM [Model] WHERE ( { [Status].[Id].& } ) ...I'm getting this resultset... ╔═════════╦═══════════╗ ║ ║ ItemStock ║ ╠═════════╬═══════════╣ ║ Console ║ 50 ║ ║ PC ║ 40 ║ ║ Server ║ 10 ║ ║ STB ║ 80 ║ ╚═════════╩═══════════╝ I was expecting that items in the ItemStock table would be filtered out by **Part** table as a many-to-many relationship. E.g. MBO, Processor and Adapter have references to items 1 and 4, so the result would be constrained to them, and the result should turn out like this: ╔═════════╦═══════════╗ ║ ║ ItemStock ║ ╠═════════╬═══════════╣ ║ Console ║ 50 ║ ║ PC ║ 40 ║ ╚═════════╩═══════════╝ What am I doing wrong?

Let me start with the background, I was working on a project that needed a back end that was scalable and fast. Having worked quite a bit with SSAS, I thought I'd try SSAS's in memory solution, Relation OLAP. Long story short, it ended very poorly, even simple queries redlined the processor, there was no multithreading, and the amount of memory used constantly grew. Barring ROLAP being a lemon, I must have messed up my implementation, and I had some questions on best practices and how I can avoid the pitfalls in the future. First up multi-threading, was there a setting I missed to enable multi-threading, because SSAS would nail one processor, and leave the other 7 untouched. Could it be Hyper V messing us up? All of our development servers are virtual, we knew this could be a problem for an in memory app, so we stayed within a single NUMA node, so the memory shouldn't have been cached, and we didn't get cross node traffic. Measures from mutiple tables killed performance, this was one of the big selling points for me, OLAP in a microsoft biome without having to first transform the data into a star schema. However as soon as we used queries with measures from multiple tables queries that might have taken two seconds individually, the combined queries had to be stopped after 15 minutes or caused the server to crash. Is there a best practice I missed about only having measures in a single table, or could this be caused by the relations between tables? Is a many to many relationship getting support in SSAS 2016? Why does the memory used grow continuously, because we only had four queries, so it's not having to create caches for a ton of queries. We did a default reprocess every night, and a full on weekends. A full process only took 7 minutes (which coming from multi dimensional SSAS was jaw dropping fast) and the intial RAM usage was just over 4 gigs. By the next day it would be between 12 and 16 gigs. Is there a memory setting I missed that capped the growth, or released resources after they were finished being used? MDX and DAX seemed about equal performance wise (in terms of speed and resource usage), but I'd read that running MDX against ROLAP can cause performance issues, for people who haved used both is there really that big of a performance hit? I have lots more questions, but I'll start with just these. The data is proprietary so it's hard to give detailed implementation diagrams, every relation was PK to FK with one exception where I was forced to use a composite key due to the restrictions against many to many relations. The composite was handled before the ROLAP in the views that the ROLAP was pulling from. I suspect the size of that key might have been one of the things slowing queries down since it was varchar(24). Any advice would be much appreciated, I loved working with ROLAP, and dax was weird but still easier than MDX.

I needed a little help on working with MDX and DAX. Recently I have just started working with these two terms and am having a little trouble figuring out the differences and possible examples of how they work. The articles I have read online are giving me a little trouble and I am finding it hard to understand. It would be great if someone could, maybe, kindly explain it in simple words what they are and what they do? Thank you so much for the help!

I'm working on a tabular cube in SSAS 2012 SP1 CU4. I have 3 dimensions (Requisition, Requisition Status, Date) and 1 fact (Requisition Counts). My fact table is at the grain of requisitionKEY, RequisitionStatusKEY, SnapshotDateKey. I have calculated measures that essentially get the lastnonempty value (like a semi-additive measure) for the given period whether it is Year, Month, Or Date: Openings:=CALCULATE(Sum('Requisition Counts'[NumberOfOpeningsQT]), Filter('Date','Date'[DateKey] = Max('Requisition Counts'[SnapshotDateKEY]))) This works well until you throw Requisition Status into the mix. I have rows for each requisition for every day in 2013. For one of the requisitions, the Requisition Status was Pending for the first 6 months and then it changed to Approved on all data from July 1 to date. When I summarize the number of openings for the requisition at the month level for July, users see two rows: the sum of the openings for the last populated day in July that it was pending and the sum of the openings for the last populated day in July that it was approved. Although the total of 2 is correct, I need to change this calculation so that I only get the most current requisition status for the date period selected (Approved) and either show 0 or null or blank for the Pending approval line in the pivot table. The Requisition Status table looks like this: Update: Here is a link to a PowerPivot model I made that has some scrubbed data in it to help answer this question. This should better explain the table schemas. The NumberOfOpeningsQT field is basically the number of open positions they have for that job. Sometimes it is 1, sometimes is is more than 1. It doesn't usually change over time, but I guess it could. I'm trying to make the Openings calculation give me the desired answer. I threw some other calculations in there so show some things I had tried that had promise but that I couldn't get to work.