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 🙂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
Measure With Beginning YTD Value even if No Fact Row
All - I have a sales dashboard that is based off a contract fact table - linked to that Fact table is a Date dimension and a Salesperson Dimension. I need two calculations - one of which should come directly from the first, but need not as long as I can fill in the value. Each contract has an effective date and a contract amount - I need to create the following: 1. A YTD Contract Amount by Salesperson 2. A Measure reflecting the Total Expected Quota they should have at that time of the year (A KPI will be created as a % of Actual / Goal) My issue is that for each salesperson they do not always sign or close a contract in a given month. Let's say their 2021 Annual Quota was $12,000 - meaning for each month they should have $1K as a goal - by July this person should have a Goal of $7K, if the actual is $14K then they are at 200%. For Salespersons that have monthly closed contracts this works fine - or even ones that have at least one record in 2021 as YTD carries forward. BUT if a Salesperson does not have a closed contract in Jan or Feb then the Actual is Blank (instead of $0 and the Goal is also blank, because the numerator is missing) Is there any way to force a $0 record to show in the months YTD that have yet to see experience? See the Yellow box in the picture below - the reason I need them to count, other than continuity - is I also need their Goal to be part of the Aggregate - For January there should be 2 SalesPersons with $150 K goal each, meaning the Total Goal should be $300K, but instead it only adds to 150K. Note: I don't want a show all option, as the dates for the quota would be very specific to Hire Date and Term dates, so there are times when a blank is appropriate and I will work that into the final answers. Also there are other account owners that I do not want showing up because they have no contract value associated. The Measure + $0 and Show Blank Values options display any account owner regardless of certain criteria. For Hired Employeed that should have value I want a $0. Thanks ahead of time, Derek
