Optimize power bi by only sending queries 1 time
I have 4 measures: Sales CY = CALCULATE(SUM(FACT_Invoice[TRX Net Sales AMT]),DIM_Date[Fiscal Year] = YEAR(TODAY())) Sales PY = CALCULATE(SUM(FACT_Invoice[TRX Net Sales AMT]),DIM_Date[Fiscal Year] = YEAR(TODAY())-1) Sales YoY = VAR _Sales_CY = [Sales CY] VAR _Sales_PY = [Sales PY] RETURN _Sales_CY - _Sales_PY Sales YoY % = VAR _Sales_CY = [Sales CY] VAR _Sales_PY = [Sales PY] RETURN DIVIDE((_Sales_CY - _Sales_PY),_Sales_PY) Requirement is that I show all 4 measures in a table visual, each have to be in their own column of a visual to allow sort by all 4 columns. When I try to optimize the Dax using Dax studio I find that the YoY and YoY % measures are taking the longest naturally. All I need to do is sent two simple queries one time to the engine. Sales CY and Sales PY. From there I wish I could just use the cache to run by Sales YoY and Sales YoY %. Instead I am sending 4 queries and having to calculate Sales CY and Sales PY multiple times and don’t want to do this. I was really hoping visual calcs would solve this issue where it would run the other queries then super quickly do simple thing slike subtracting. Essentially I want to only send queries necessary. Then for YoY or YoY % I want to simply just use the already run queries. I have so many use cases for this. I want to only query a measure once but then use it in multiple columns in different ways. Any thoughts?
How Filtering by multiple typeValue for the same Type
Hello, So this is my problem. I have a data set like FactData : [ID BIGINT PRIMARY KEY, CreateDate DATE, CaseCount INT, CaseValue DECIMAL(10, 2), EffectiveDate DATE, Type_A INT, Type_B INT, Type_C INT, Type_D INT, Type_E INT, Type_F INT, Type_G INT, Type_H INT, Type_I INT, Type_J INT, Type_K INT, Type_L INT, Type_M INT, Type_N INT, Type_O INT, Type_P INT, Type_Q INT, Type_R INT, Type_S INT, Type_T INT, Type_U INT, Type_V INT, Type_W INT, Type_X INT, Type_Y INT, Type_Z INT] And DimType : [ID INT PRIMARY KEY, Type CHAR(1), Value INT] I need to filter my FactData with my DimType but my fact can have multiple type and I must can select multiple types. This is my code but it not really quick, it take 15s to run across 5 million rows. Filtered Quantity = VAR _SelectedTypes = VALUES('Type'[ID]) RETURN IF( ISEMPTY(_SelectedTypes), [quantiter], CALCULATE( [quantiter], KEEPFILTERS( FILTER( 'FactData', ( FactData[TypeA] IN _SelectedTypes || FactData[TypeB] IN _SelectedTypes || FactData[TypeC] IN _SelectedTypes || FactData[TypeD] IN _SelectedTypes || FactData[TypeE] IN _SelectedTypes || FactData[TypeF] IN _SelectedTypes || FactData[TypeG] IN _SelectedTypes || FactData[TypeH] IN _SelectedTypes || FactData[TypeI] IN _SelectedTypes || FactData[TypeJ] IN _SelectedTypes || FactData[TypeK] IN _SelectedTypes || FactData[TypeL] IN _SelectedTypes || FactData[TypeM] IN _SelectedTypes || FactData[TypeN] IN _SelectedTypes || FactData[TypeO] IN _SelectedTypes || FactData[TypeP] IN _SelectedTypes || FactData[TypeQ] IN _SelectedTypes || FactData[TypeR] IN _SelectedTypes || FactData[TypeS] IN _SelectedTypes || FactData[TypeT] IN _SelectedTypes || FactData[TypeU] IN _SelectedTypes || FactData[TypeV] IN _SelectedTypes || FactData[TypeW] IN _SelectedTypes || FactData[TypeX] IN _SelectedTypes || FactData[TypeY] IN _SelectedTypes || FactData[TypeZ] IN _SelectedTypes ) ) ) ) ) So how can I improve it? I have made a pbix for example with a csv file for data. But I don't know if I hoe to share it.HELP!! Add period 0 to calculation without putting it in slicer
Hi everyone, I've been running into a problem for a while now. I am currently working on a financial balance sheet. Currently my dataset consists of the following columns: - Description - Year_Open - Period credit (These are the changes to the opening value) - Period debit (These are the changes to the opening value) - Period (1 to 12 months) As can be seen in the photo above, the opening value is not a period, I wanted to give it the period 0. This made me want to create the formula: Period 0 + selected period in slicer. Does anyone know how I can create this formula in DAX? I also have to ensure that the period 0 is not visible in the slicer, how can I do that? However, I am not very good with DAX formulas and so far I have solved it as shown below. Only then will the total not change if I adjust the slicer, even though this is a requirement. Credit = VAR PeriodCredit = SUM( 'Balance'[Year_Open]) + SUM('Balance'[Period_Debit]) - SUM('Balance'[Period_Credit]) VAR Scoop = NOT(ISINSCOPE(KPI_EOL_xml_GLAccountClassifications[GLClassification2_description])) VAR ScoopPeriodCredit = SUM(Balance[YearClosedCredit]) VAR Result = IF(Scoop,ScoopPeriodCredit,IF(PeriodCredit < 0, - PeriodCredit)) Return Result Hopefully someone can help me with this! Thanks!
Optimized DAX code for calculating on different Filter Context
Hello. I've encountered a Sencario in Which I have to calculate each Value in different FC(Filter Context) based on predefiend Scenarios of A and B. here is my Data Structure in Excel. in above image, [OldCost] and [NewCost] are two Calculated Columns and are the result of [Last Purchase Rate]*[Standard Consuption Rate] and A and B are two Measures calculated by DAX by the following Code snippet for B: = VAR test10 = IF( MAX( BothBOM[ItemCode] ) <> "0501-2020", 0, SUMX( SUMMARIZE( FILTER( ALLEXCEPT( BothBOM, BothBOM[ProductCode] ), BothBOM[ItemCode] = "0501-2020" || BothBOM[ItemCode] = "0501-2014" ), "total", CALCULATE( SUMX( BothBOM, BothBOM[NewCost] ), BothBOM[ItemCode] = "0501-2020" ) - CALCULATE( SUMX( BothBOM, BothBOM[OldCost] ), BothBOM[ItemCode] = "0501-2014" ) ), [total] ) ) VAR test9 = CALCULATE( SUMX( BothBOM, BothBOM[OldCost] ), KEEPFILTERS( LEFT( BothBOM[ItemCode], 4 ) = "0502" ) ) VAR test11 = SUMX( SUMMARIZE( FILTER( ALLEXCEPT( BothBOM, BothBOM[ProductCode] ), BothBOM[ItemCode] = "0501-2020" || BothBOM[ItemCode] = "0501-2014" ), "total2", CALCULATE( SUMX( BothBOM, BothBOM[NewCost] ), BothBOM[ItemCode] = "0501-2020" ) - CALCULATE( SUMX( BothBOM, BothBOM[OldCost] ), BothBOM[ItemCode] = "0501-2014" ) ), [total2] ) RETURN IF( NOT ( HASONEVALUE( BothBOM[ItemCode] ) ), test11 + test9, test9 + test10 ) in summary, in senario B (VAR=test10), I have to calculate for ([ItemCode]="0502-2020") as : ([ItemCode]="0502-2020") minus ([ItemCode]="0501-2014") e.i (433.000-146.000=287.000) and VAR=test11 stands for calculating subtotals in different context. the outcome of these DAX is as expected, but I am wondering if there might be a better solution and better DAX coding?Additionally , I'm curious about the efficeny of this code. specially for VAR=test11 by which I've handeled subtotals for the Measure.Measures taking a long time - need help to optimize
Greetings PowerBI community, This is my first post and I have been struggling with this for a while. I will post a lot of screenshots and try to explain the issue. So basically I need help to improve these measures to run faster. I do not understand what is making them so slow. Please help. I basically have two measures that are running a bit slow according the performance analyzer. These measures are [Coverage weighted] and [Coverage numeric] Coverage numeric = DIVIDE(fmInvoiceSales[Active Customer Buy],[Active Customer]) Coverage weighted = DIVIDE(fmInvoiceSales[KFP SM Weighted Sales],fmInvoiceSales[KFP SM Every Sales]) These measures are simple DIVIDE's however its the 4 measures within these DIVIDES that are heavy. One of the DAX Queries that is taking a long time looks like this. It runs particulary slow when using the slicer "Sales Region" which comes from the dmCustomer table. // DAX Query DEFINE VAR __DS0FilterTable = TREATAS({"Horeca Sweden", "North"}, 'dmCustomer'[Sales Region]) VAR __DS0FilterTable2 = TREATAS({"Senaste 52 veckor"}, 'Period Parameter'[Period]) VAR __DS0FilterTable3 = TREATAS({TRUE}, 'dmCustomer'[Active Store CRM]) VAR __DS0Core = SUMMARIZECOLUMNS( 'dmCustomer'[Sales Region], __DS0FilterTable, __DS0FilterTable2, __DS0FilterTable3, "Coverage_weighted", 'fmInvoiceSales'[Coverage weighted], "Coverage_numeric", 'fmInvoiceSales'[Coverage numeric] ) VAR __DS0PrimaryWindowed = TOPN(1001, __DS0Core, [Coverage_weighted], 0, 'dmCustomer'[Sales Region], 1) EVALUATE __DS0PrimaryWindowed ORDER BY [Coverage_weighted] DESC, 'dmCustomer'[Sales Region]
Is there a better way to handle this date control in my measure?
Before I created my memberbase on historical subscriptions per member. That was a small table consisting of 250.000 rows and this measure to show historical development over time worked well: Memberships= Var MaxDate = MAX ( DimDate[Date] ) RETURN CALCULATE( CALCULATE ( COUNT ( FactDonor[#Donornummer] ), FILTER ( FactDonor, ( FactDonor[CreateDate] <= MaxDate && FactDonor[EndDate] > MaxDate ) ), ALL ( DimDate ) ), CROSSFILTER ( FactDonor[CreateDate], DimDate[DateKey], NONE ) ) Now I had to add more change to members, so that I for each row look at membership date as well as changes to other parts such as adress and member-specific things. That means that I have to create lines with change date within the membership dates. It only for added a little over 100.000 rows and my measure now looks like this: Membership = Var MaxDate = MAX ( DimDate[Date] ) RETURN CALCULATE( CALCULATE ( DISTINCTCOUNT ( FactDonor[#Donornummer] ), FILTER ( FactDonor, ( FactDonor[CreateDate] <= MaxDate && FactDonor[EndDate] > MaxDate ) ), FILTER ( FactDonor, ( MaxDate >= FactDonor[ChangeStartingDate] && MaxDate <= FactDonor[ChangeEndingDate] ) ), ALL ( DimDate ) ), CROSSFILTER ( FactDonor[CreateDate], DimDate[DateKey], NONE ) ) My main issue is that the measure is way slower, when creating a chart with memberships per month since january 2021. So is the new measure written correctly or would You handle the change part differently?Can this DAX be optimized?
Hi I wonder if this Measure can be optimized a bit? I works as intended, but is a bit slow Seneste betalingsmetode = VAR SenestBetalt = CALCULATE( MAX(FactBetalingslinjer[DatoKeyStoettePeriode]), FactBetalingslinjer[#Payed Amount (GL)] > 0 ) VAR BetaltMetode = CALCULATE( MAX(FactBetalingslinjer[BetalingsmetodeKey]), FactBetalingslinjer[DatoKeyStoettePeriode] = SenestBetalt ) VAR Metode = CALCULATE( MAX(DimBetalingsmetode[BetalingsmetodeNavn]), DimBetalingsmetode[BetalingsmetodeKey] = BetaltMetode ) RETURN Metode I need to find the latest period based on latest payment (Variable SenestBetalt) Then from that date I like to find the payment type (Variable BetaltMetode) Finally I get the paymeny name from the dimension (Variable Metode) The final part is needed (I think) because it's a starschema with all dimensions pointing at FactBetalingslinjer, so I can find the key and then get the name from the dimension. But can it be done smarter? and can it be done faster. Thanks in advance 🙂Need help improving DAX calculations for accurate results in Power BI from single date column!
Hello Power BI community! I'm seeking your expertise to help me improve two DAX calculations that I'm currently using to calculate the receiving and sending (handling) dates for a ticket table. The data I'm dealing with is related to a list of tickets that are received and then treated by technicians on different dates, unfortunately, I'm only provided with one date column which aggregates all actions performed on a specific ticket (which is DateTime), and a ticket may appear repetitively but with different receiving and sending dates (which should be taken into account of course). Here's a snapshot from the raw data table: Feel free to download these tables from here: "https://smallpdf.com/file#s=c745e973-29a0-4dbd-85ab-2472d7379858" Below are the DAX calculations I'm using to produce the results (which are now inaccurate) ReceiverDate = CALCULATE( MIN(WEEKLY_IDs[DateTime]), FILTER( ALL('WEEKLY_IDs'), 'WEEKLY_IDs'[SenderID] = EARLIER('WEEKLY_IDs'[ReceiverID]) && 'WEEKLY_IDs'[ReceiverID] = EARLIER('WEEKLY_IDs'[SenderID]) && 'WEEKLY_IDs'[Ticket_ID] = EARLIER('WEEKLY_IDs'[Ticket_ID]) ) ) SenderDate = CALCULATE( MAX([DateTime]), FILTER( ALL('WEEKLY_IDs'), 'WEEKLY_IDs'[SenderID] = EARLIER('WEEKLY_IDs'[SenderID]) && 'WEEKLY_IDs'[ReceiverID] = EARLIER('WEEKLY_IDs'[ReceiverID]) && 'WEEKLY_IDs'[Ticket_ID] = EARLIER('WEEKLY_IDs'[Ticket_ID]) ) ) These are the end results I'm seeking to accomplish with these two DAX calculations:
