help DAX dataset on ssas tabular for reporting services
I need to write a DAX query for a reporting services report the data source is a ssas tabular cube the datamodel is kimball dim model requirements The dataset needs to include columns from one fact table and two related dimension tables only a few columns from each table shall be included the dataset shall be filtered from SSRS parameters (both fact and dimension table) baically what I need is help writing a query that joins the three tables (inner join) as efficient as possible I am elaborating with selectedcolumns, different types of joins (related, naturalimnerjoins, lookup) and filter function I would approciate some input here
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 1Check if all values exist
Hi there, Here is the situation : I have contractlines, which hold an activity code. It looks like this : Contractnumber is 4864, and the activity codes are O03 and O29 Now I have orderlines which contain an activity code and an contractnumber, that looks like this : The question is . . how do I check that ALL the activity-codes from the contract are in the order ? It can be that an order holds more activity codes than the contract, which is okay, but it should return true in that case as well. All help is welcome Thanks in advance. JaccoCircular 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))How to convert a String YYYY-WW to integer YYYY-WW column in DAX Studio?
Hi I am trying to import a table the contains last 4 weeks of data. The planning week column is a String column and has the yyyy-ww format. Is there a way to convert this column to an integer YYYY-WW column in DAX Studio and import the last 4 weeks of data? Note: No date column in the tableValor Máximo en Columnas Calculada.
Estoy creando una columna calculada con DAX en un modelo tabular para encontrar el valor maximo en la filas Ejemplo: Logre, encontrar el valor por columnas, pero necesito encotrar las columnas de forma dinamica, ¿alguna segerencia? Esta es la medida de Dax, que estoy usando: = MAXX( VALUES(SaldoSafix_MoraMaxima[cedula]), CALCULATE( MAXX( (SaldoSafix_MoraMaxima), CALCULATE( MAX(SaldoSafix_MoraMaxima[2021-6]) ) ) ) ) GraciasDisplay Last Massage
Hello 🙂 i have this column or measure to do and i really don't have an idea to how to make it. here is the story: the client asked me to display the last massage attached to an ID 3 columns -Tag_Analyse(id_Tag,id_typemessage,id_date_of_booking) -Type_massage(id_typemessage, type_message, libelle_message) -Date_of_booking(id_date_of_booking,Date,year,moth,hour) my client want to display the last message(libelle_message) attached to an id_tag on certaine Date like if my booking went well --> the last message display should be booking ok(i have all this set on my type message column under message) can someone help me to buil the dax code please 🙂SSAS Tabular - Default Detail Rows Expression
For a SSAS 2017 tabular model surfaced via pivot tables, a handy (vital) feature is the ability to drill through to a sensible selection of detail rows via double click on a measure (just like in the old school Excel pivot tables that your finance department can't part with). The content of the drillthough is determined by the table property [Default Detail Rows Expression]. The example Microsoft provides here is great insofar as it goes. SELECTCOLUMNS( 'Internet Sales', "Customer First Name", RELATED( Customer[Last Name]), "Customer Last Name", RELATED( Customer[First Name]), "Order Date", 'Internet Sales'[Order Date], "Internet Total Sales", [Internet Total Sales] ) My problem is that I have a calendar table with an active relationship with my fact table (let's call this fact column end_date_sk) and an inactive relationship with the same table (let's call this fact column start_date_sk). If I want to return calendar columns from both the inactive and active relationships, what do I do? RELATED() only seems to work for the active relationship.
