SSAS Tabular Model performance Issue
Hi I have a SSAS tabular model of almost 2 GB size. I refresh it's data using Azure Analysis Service on S1 tier(P.S. I have tried with S2 tier too but it still consume same amount of time) and refresh request is generated through ADF. Earlier it was working fine and refreshing data with 40 mins for all requested partitions. Now someone has added few new DAX measures to my model and since then me refresh time has increased to 3 hours. I want to optimize my model and reduce refresh time. I am only refreshing the relevant data(current data not the historical data). How can I reduce refresh time and optimize my tabular model. I tried reducing the data but still its taking long time to process the data which is not the ideal case. I guess the effectiveness is effected due to any DAX measure addition but still its just a guess. I dont know the method to identify the cause as I have too many DAX measures in my model.
DAX Expression to choose SSAS tabular model Measure based on Dimensional table selection.
I have desgined a tabular model cube in SSAS on transactionas. We have two facts one for transaction and one for offset transaction and there are multiple dimension tables. FACT_A - Transactions TRN_FACT_ID, POSTED_DATE, TRN_AMT 1 20221001 5000 2 20221001 10000 FACT_B - Offset Transaction OFFSET_FACT_ID, TRN_FACT_ID, Posted Date, TNR OFFSET Amt 234 1 20221001 3000 345 1 20221001 2000 456 2 20221001 10000 So we are not using FACT_B as a fact table in tabular model instead we are using it as a Dimensional table. But requirement is to get offset tran count as a measure. I did a count(*) and grouped tran_fact_id from FACT_B and left joined it to fact_a on TRN_FACT_ID to bring in offset count and created a measure on it. Cube results(when only offset count is pulled with transaction) TRN_FACT_ID, POSTED_DATE, TRN_AMT, OFFSET COUNT 1 20221001 5000 2 2 20221001 10000 1 Cube results(If i pull in any attribute from Offset table) TRN_FACT_ID, POSTED_DATE, TRN_AMT, TNR OFFSET Amt, OFFSET COUNT 1 20221001 5000 2000 2 1 20221001 5000 3000 2 2 20221001 10000 10000 1 Cube results(Expected results) (Offset count should be 1 on each line when an attribute is selected from offset table if not count should be 2) TRN_FACT_ID, POSTED_DATE, TRN_AMT, TNR OFFSET Amt, OFFSET COUNT 1 20221001 5000 2000 1 1 20221001 5000 3000 1 2 20221001 10000 10000 1Creating relationship between two tables using null calculated columns SSAS
Hi! I'm having a problem of recreating a solution that my previous colleage did and nobody has no idea what the solution really means. There are two tables in tabular model: a dimension (D) and a fact table (F) which have the same source but each table contains different number of attributes. Each table contains a calculated column "Link" which has no expression: These two tables has a relationship (Many to one, Both Directions) using these two calculated columns: How does it even work? Even though I don't understand the logic, I tried to recreate this solution using another two tables, but I'm getting an error: Link column contains a duplicate value null and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table. Can somebody please explain what is going on and why am I getting an error while trying to recreate almost the same thing that actually works? Thank you in advance!
Measure in Live Connection - Incorrect Total
Hello, I know there have been many solutions on incorrect totals from measures in Live Connection, but I can't seem to figure out the solution for my measure: No of Sessions = VAR _encounters = CALCULATE ( [Amount], FILTER ( ALL ( 'Profitability Account' ), 'Profitability Account'[Description] = "Encounters" ) ) VAR _NoOfDays = Calculate(DISTINCTCOUNT('Time'[CalendarDate]),Filter ('Time', 'Time'[DayOfWeekdayDescription] <> "Sunday" && 'Time'[DayOfWeekdayDescription] <> "Saturday" )) VAR _NoOfSession = _NoOfDays * SWITCH ( TRUE (), _encounters <= 2, 0, _encounters >= 2 && _encounters <= 10, 1, _encounters > 10, 2 ) RETURN _NoOfSession This gives me the following result (I have covered up the practice locations): You can see the No of Sessions total is incorrect. I even attempted something different by trying something different. I wanted to try something like DISTINCTCOUNTX (but this does not exist in DAX) - so I found the below solution: No of Sessions New = VAR _NoOfDays = COUNTROWS( DISTINCT( SELECTCOLUMNS( 'Time', "CalendarDate",'Time'[DayOfWeekdayDescription] <> "Sunday" && 'Time'[DayOfWeekdayDescription] <> "Saturday" ))) VAR _NoOfSession = _NoOfDays * IF ( [Encounters] <= 2, 0, IF([Encounters] >= 2 && [Encounters] <= 10, 1, 2 )) RETURN _NoOfSession I replaced the VAR _encounters with a measure instead [encounters] . And I replaced the SWITCH statement for an IF statement instead to see if that will do anything different, but it still doesn't work: The following is the Encounters measure (it's exactly the same as in the original No of Sessions DAX: Encounters = CALCULATE ( [Amount], FILTER ( ALL ( 'Profitability Account' ), 'Profitability Account'[Description] = "Encounters" )) Please help me write a new DAX measure that will fix this issue and give me correct Totals 🙂Impact analysis of deployed on-premise reports on tabular model
What tool do you advice to use when performing impact analysis on-premise? So for instance when changing the name of a field within the tabular model, i would love to find all reports which will be impacted (the reports using the tabular model with the specific field in a dataset).Circular dependency error... from data in a same column?
Hey, community! I have some trouble with a "Circular dependency", let me tell you about it: In Microsoft Excel I am considering columns and cells to make specific sums in a calculated column, here's an image as an example where the column "G" is where I want to show the result of the sums made: Now, in Microsoft Excel there's no trouble with it, everything is fine, the real problem is when I want to do the same thing in Power BI because the app tells me there's an error called "Circular dependency". Here's the image that shows what's going on: So, my two questions are: How can I avoid "Circular dependency" in Power BI so the values in the same column where I am making sums can also add themselves? Taking as an example the image in Microsoft Excel, if the number from the cell "C2" can be found several times in column "E", the respective amounts from the column "G" will sum, is there a way in Power BI where I can select a whole column and a single cell from a column at the same time? (from what I've tried, I can only select columns, but I want to select cells or rows as well) I'm expecting that the calculated column from my Power BI tabular model show the sums corresponding to the conditions I'm asking: "If the IDs from a column match the ID from a single row, the respective amounts that match will sum, ending this cycle by not finding the next ID and showing me as a result the final amount from that particular row". Thank you. P.S.: Here's the formula made in Excel: =IF(NOT(F2=0),F2,IF(NOT(OR(D2="I",D2="G")),SUMIF($E$2:$E$1048576,C2,$G$2:$G$1048576),0)) And here's the formula made in Power BI: GLCurrentYearCalculated = IF(NOT('Table'[GLCurrentYearBeginningBalanceInput]=0),'Table'[GLCurrentYearBeginningBalanceInput],IF(NOT(OR('Table'[GLBalanceType]="Income",'Table'[GLBalanceType]="Expense")),IF('Table'[GLAccountUse]='Table'[GLAccountNumber],SUM('Table'[GLCurrentYearCalculated]),0),0))Filter Dimension in Tabular model
Hi, I know this isn't strictly Power BI, but maybe someone in here are able to help. I have a number of Tabular models, where I'd like to restrict which dimension values a user can see. I already have Dynamic RowLevel Security defined to restrict the Fact table, but since it's a "global" Tabular model that contains data for several companies, I'd like to "filter out" the dimension values for the user so he don't have to see data for other companies than his own. The DRLS is created ,so it matches the userID in a table, and this then filters a Legal Company table which then have a relationship to the Fact table. That part works fine and the user only see fact records from his own legal company. I'd then like to so something similar with the dimension data, but since I can't create relationships from the Legel Company table to the dimension tables, then I was thinking about doing it in the Security Role. I was thinking about a rule looking at the Legal Company for the dimension and then "filtering" based on the data from the Legal Company table that only contains the legal company the user are allowed to see. I'm not that experienced in DAX yet, and I can't really figure out how to do it? I have tried various scripts with no luck, so I'd be happy with some idea and guidance. Regards Steen
SSAS Tabular Model DAX measure in paginated report with incorrect result
A paginated Report has a parameter to select a month and year, e.g. March 2021. The following Dax measure through SSAS Tabular Model in a dataset created by Report Builder shows the correct sum: Value1 : = calculate(sum(table1[value1])) Another Dax measure should list the sum of dezember of previous year: Value1 Dez PY : = calculate([Value1], lastdate(previousyear(lastdate(table1[Date]))) ) Table1 contains rows for each product and month like: Date(mm.yyyy) Product Value1 12.2020 A 100 03.2021 B 110 In 03.2021 there is no record for product A. The report output should looks like this: Product Selected Month Last Month Previous Year B 110 A 100 Actually the report does not show the product A and value for Last Month Previous Year Product Selected Month Last Month Previous Year B 110 Any idea how to get also Product A which is not available in 03.2021?
