Tabular Editor - Field Parameters - fields from more than 1 table
Hi Is it only possible to add fields from one table to create parametrs via Tabular Editor? In Analysis Services connected to a date warehouse I have various dimesnions which I would like to be selectable (customer, state, channel, salesperson etc). I have tried using the C# script posted by Daniel Otykier however seems it is limited to selecing fields from 1 table only. Is this correct?
Divide one row with the sum of two other rows
Hello Community, Is it possible to divide the sum of Scrapped Qty with Manufactured Qty and place the reslut in the first row of Quality Index i.e (50 + 150) / 1000 = 0.8. Right now it takes 50/1000 = 0.95 So what I have done is the following: Manufactured Qty Filtered :=SUMX(FILTER('Dummy',Dummy[FirstOperation] = 1),[Manufactured Qty]) Quality Index :=DIVIDE([Scrapped Qty],[Manufactured Qty Filtered])
Simple Division driving me crazy!
Hi all I just can't seem to understand why my tabular model is coming up with the wrong answer. All I'm doing is dividing one column by the other. ='Stock Analysis'[Physical Quantity (R)]/[Net Usage] I didn't need to use variables but I've tried lots of different ways to divide it but it keeps coming up with the same problem. 19.77686 / 0.014370 The answer is 1,978 not 1,376. Note; the first column Physical Quantity (R) is a hard number created via power query. The second number is a calcuated column which I'll include but I don't see how this should have an affect on this simple division: Net Usage='Stock Analysis'[Consumption Quantity 12 Months]+'Stock Analysis'[Sold Quantity 12 months] Consumption Quantity 12 Months=[Pick Quantity In Previous 12 Months] Pick Quantity In Previous 12 Months:= VAR CurrentDate = TODAY() VAR PreviousDate = Date(Year(CurrentDate),MONTH(CurrentDate)-12,DAY(CurrentDate)) VAR RESULT = CALCULATE( SUM([QtyReporting]), FILTER( 'Primary Date Table', 'Primary Date Table'[Date] >= PreviousDate && 'Primary Date Table'[Date] <= CurrentDate ) ) RETURN RESULT Thank you!DAX formula to calculate different calculation
Hi everyone, I am reaching out to experts and peers here to get an idea and hopefully a solution. My environment is SSAS Tabular 2016 Enterprise Ediiton, and my tabular model has two tables, for simplicity. Power BI and Excel will be our front end visualisation tool. In the future, we will be looking at using AAS/Premium. Fact: Injury Count Reporting Date Key Dates (Date dim) Date Key Day, Week Month Name Year Month Indexer (to sort Month) My business users will report Injury Counts for day, and week. For daily counts they want a SUM of all counts. But for weekly counts, they want the count on the last day of the week. That's because, the weekly count represents how many injuries were there at the end of the week showing the measures taken. The report should also enable drill down from Week to Day. I am looking at following options 1 - Have two different tables in my model. Week and Date. Both of them will have Date keys so I should be able to drill through from Week to Date using Date (Date Ke). 2 - Keep one table with different measures for Day and Week calcs. I have about 18 measures right now, and they are expected grow to about 50+. Maintenance wil be a bit of pain. Downside of this approach, perhaps, is that there won't be any drilldown or drillthrough, I haven't tried it. 3 - Write DAX measure to handle day and week in the same measure. for daily calculation, do a SUM and for weekly calc, pick up the last day of week's number. I have no idea how will I write it, at this stage :). I am attaching a sample dataset along with sample output on the first page. Data and output sample Thanks KazDax for multiple inner self joins
Hi guys, I need small help in writing DAX for below situation. table_A id date stagetype statustype 1042268 2020-01-27 1 1 1042268 2020-01-27 3 2 1041658 2020-01-27 2 2 1017296 2020-01-27 3 15 1042269 2020-01-27 1 1 1042270 2020-01-27 1 1 1041053 2020-01-27 3 2 1030893 2020-01-27 2 2 1030893 2020-01-27 3 2 1042271 2020-01-28 1 1 The SQL query is select Count(DISTINCT R.id) AS 'ID' FROM table_A R WITH(nolock) INNER JOIN (SELECT DISTINCT SS.id, Min(SS.date) AS MinDate FROM table_A SS WITH(nolock) WHERE SS.id IN (SELECT SSS.id FROM table_A SSS WITH(nolock) WHERE SSS.stagetype = 5 AND SSS.statustype = 4) AND SS.statustype = 13 AND SS.stagetype = 5 GROUP BY SS.id) AS MinDate ON R.id = MinDate.id AND MinDate.mindate = R.date WHERE R.id IN (SELECT DISTINCT SS.id FROM Table_A SS WITH(nolock) WHERE SS.id IN (SELECT SSS.id FROM Table_A SSS WITH (nolock) WHERE SSS.stagetype = 5 AND SSS.statustype = 4 ) AND SS.statustype = 13 AND SS.stagetype = 5) Actually the query using inner self join. I need DAX for above query. Please help on this.
Select current date as default selection in slicers. (Tabular model - Report Server)
Hello, So I have a 'Year' slicer and a 'Month' slicer, I want my end-users to enter the report with the current Year and Month selected. How to achieve this? I know its possible in Power BI desktop but notice that I am having this problem with Tabular model + Report Server. Thanks in advance, JimServer Side Aggregations with POWER BI Report BUilder
Hi, I am trying to create my first report in Power BI Report Builder. I am connecting to our Azure Analysis Services (Tabular) model. The measure "Catchment Rate" is non-additive, i.e. when I display subtotals and use a sum, it will not give me the right results. I understood there is an Aggregate function, but when I use that function the cell remains blank. Is there anyone that can help out? Best, P
