Slow DAX formula to calculate # Orders Delayed with adjustment
Hi I have a datamodel with orderlines and goods receipt. I have written a formula to calculate number of unique orders delayed (in Danish # Ordre Forsinket) based on two what if parameters. The parameter 'Leveringstid Regionslager rabat'[Leveringstid Regionslager rabat] is used to adjust the delay with X number of days on those orders there are marked as indkøbsbilagsart ZCD or ZLM. The parameter 'Leveringstid Øvrige rabat'[Leveringstid Øvrige rabat] is used to adjust the delay with X number of days on those orders there are different from indkøbsbilagsart ZCD or ZLM. The data model looks like this and there is 1.4 million rows in the table 'Ordrelinjer' and 2.775 suppliers in the table 'Leverandør': Here is the DAX formula: # Ordre Forsinket = VAR _LeveringstidRegionslagerRabat = SELECTEDVALUE( 'Leveringstid Regionslager rabat'[Leveringstid Regionslager rabat], 0 ) VAR _LeveringstidOevrigeRabat = SELECTEDVALUE( 'Leveringstid Øvrige rabat'[Leveringstid Øvrige rabat], 0 ) RETURN CALCULATE( DISTINCTCOUNT('Ordrelinjer'[Indkøbsordrenummer]), FILTER( ADDCOLUMNS( FILTER( 'Ordrelinjer', 'Ordrelinjer'[Varemodtagelse] = 1 && 'Ordrelinjer'[Afvigelse i dage] > 0 ), "JusteretAfvigelse", 'Ordrelinjer'[Afvigelse i dage] - IF( RELATED('Indkøbsbilagsart'[Indkøbsbilagsart]) IN {"ZCD", "ZLM"}, _LeveringstidRegionslagerRabat, _LeveringstidOevrigeRabat ) ), [JusteretAfvigelse] > 0 ) ) The measure is used in a table visual, but it takes around 33 seconds to refresh the table visual. I need a few more measures in the table, so it will just take longer and longer time to refresh the table. Can I write the DAX formula in a more efficient, clean and performance optimized way? Best regards Morten
Table Visualization by Customer breakdown by Type and Month
Hello Power BI Community Can anyone please advise me on how to resolve this. My use-case is that we have monthly finance transactional data for each month from Jan to May (e.g. Data source is SQL), bringing in the latest data every month. My Datasets each row consists of Month, Customer, Brand, Sales Price, and Volume and Data model consist of DateTable, where the month has one to many relationships with Customer Table. I manage to create DAX showing MTD & YTD calculation for [Volume], [GM], [ARP £/MT]. Please note [Volume], [GM], [ARP £/MT] these are measures created from monthly finance transactional data. I tried Summarise and Add column DAX nothing seems to be working out, may be i'm made some error. DAX Measures for Volume = Sum('Gross Margin'[Volume]) DAX Measure for ARP£/MT = (DIVIDE([Sale Price],[Volume])) My final output would like to plot the data on a column chart by Type (Volume], ARP £/MT], [GM £/T] the columns would be stacked in each month to give the break-down by the customer by Brand. I want to create two table visualization one by MTD and another Cumulative total, without giving the grand total for ARP £/MT (Average Retail Price) & GM £/ MT, something like this (above mentioned format). Appreciate your help in advance.😊Add conditional formatting or action customization for column headers in matrix and table visuals
I would like to suggest adding the option to conditional format column headers in matrix and table visuals as hyperlinks to enable quickly navigating to a web URL or a page within the report. For example, I have a report page that acts as a heat map with various columns show the health of each client (rows) in each of the various categories (columns). Subsequent pages in the report provide more detailed metrics and information for each of the corresponding columns on the heat map. I want to make it so the user can click the heat map column header and be redirected to the corresponding details page in the report. This is one of many instances where being able to conditional format a column header with an action or hyperlink would be helpful. For the above example, I do have a workaround in place, using "invivisible" action buttons, however these do not move with the headers in the event we need to scroll to the left or right in the matrix or table visual so I am limited to only showing the number of columns that can fit in the view without scrolling left to right.
Aggregated values directly at the last rows of TABLE visuals
Hi, Being a newbie requesting help to get the aggregated values below the "total" row generated from TABLE visual. Unable to use MATRIX visual as i need to show the aggregated values below the total(sample representation as below).Will use the same for Date wise as well as month wise ,year wise slicer. Data Set Sample as below: Also i have created Aggregated Table using Power query which is available as a data set: Also have created measures on values for each measuing point separately which is used for other graphical visuals. Deeply appreciate forums support to meet the objective. Thank you.
Creating excel like cell-based dropdown in Power BI Table
Hi, I am new to Power BI and was looking for ways to create a status update table in Power BI to track progress of certain tasks. The table in excel would look like this I want to create a similar table where I would be able to update the status in the visuals itself. Is that possible to do in Power BI? Thanks in advance for any help!
Aggregate measure on two field of a visual table
Hi, I'm trying to replicate this table format on PowerBI table visual: I have created Measure1 as follows: Measure1 = CALCULATE(COUNT('Table'[Request Number])) and I put it on G column that is the value of each Field2 in column F in each month. What I want to do is to calculate column E by the same measure grouped by Field1 (column C) for each month. So for example on the first row of column E we have 11 for Field1=X and Month=January, that is 6+4+1 ofthe first three rows Column G, and so on. I tried the GROUPBY or ALLEXCEPT functions by Month and Field1 but it didn't work. How can I build the formula or use particular view for the visual to aggregate the column E by month and Field1?
