DAX to concatenate text values in a column
Hello, I am seeking assistance for an appropriate measure to concatenate certain text values in a column to then = a single text value. This would be applicable to the listed items below from a single column. I am looking to acheive the following: Site page, BMS Navigation Page, BMS BPAD = Web Page Policy, Framework, Plan, Manual, Procedure, Instruction, Guide, Handbook = Documents The remainder of the values in the column would remain as is. Any assistance would be much apppreciated! 🙂
Calculated column Shows Repeated Values
Hello All, I am working on a measure but im getting repeated values across all the months, can you please assist on what I am missing on the dax commands? Thank you!! I am using the following measure DAX item SUBTOTAL = var currentfrom = selectedvalue(Layout[FROM]) var currentto = selectedvalue(Layout[TO]) return calculate( [Actual value], FILTER( ALL('Act'), 'Act'[PK]>= currentfrom && 'Act'[PK]<= currentto ) ) Relationships on Layout to Act on Primary Key (PK) and Act to Date2 on Date Act Table Layout Table Statement Level 1 Level 2 Primary Key Calculation Table FROM TO Highlight Type Operator Main Income Statement Line Blank Line Depth Income Statement Revenue Contracts 186 SUM 186 186 1 1 TRUE 2 Income Statement Revenue Materials (Repairs Only) 187 SUM 187 187 1 1 TRUE 2 Income Statement Revenue Shipping, Freight 188 SUM 188 188 1 1 TRUE 2 Income Statement Revenue Service Repairs 189 SUM 189 189 1 1 TRUE 2 Income Statement Revenue Discounts 190 SUM 190 190 1 1 TRUE 2
Dax Column - Copy Date from a defined column and IF blank... copy date from another defined column
Hi all, My first time posting and I'm new to PBI, so apologies if this is a little unorganized. Below is a snapshot of my data table. The outcome I am looking for is to show in the "Date of Activity Logged - Copy" Column the "Date of Status Change" date for this row and IF this is blank... to show the "Date of Activity Logged" date for the same row within the "Date of Activity Logged - Copy" also. Unfortunately the source of this dataset splits these two dates into different cells, when the preferred outcome is that only the most recent date of the two is shown in a column... if that is a different way to view the issue? Any help on this is appreciated. Many thanks Matt
Optimising measure to replace URL column
I have a dataset with a table containing barcodes, and I have been generating a URL from these with a simple concatenation in SQL prior to import by my Power BI dataset. e.g.: 'https://first-part-of-url.com/'||"Barcode" That links to the item's page on my company's internal system. It's mainly used in tables within drill-through or detail pages in my reports. All my tables have been imported, no DQ. I was looking at the size of my dataset and saw that each URL was taking about 10 - 15% of my dataset size. That makes sense given the number of unique values of that field. So I thought I'd use a measure that could be used in those tables. I was aware I'd be reducing size/load speed at the expense of load times within the report. Here's the measure I drafted: Item URL = IF( HASONEVALUE('Item Summary'[Barcode]), "https://first-part-of-url.com/" & VALUES('Item Summary'[Barcode]) ) I did some benchmarking on DAX studio and on a test table query the duration of the query went from 67 ms to 1365 ms. That jump in load time is higher than I was expecting. Is there a more efficient DAX expression to replace this column with a measure? Thank you!
Monthly Comparison with yearly average
I need to create a table or matrix that breaks the data into 4 columns: this month, last month, average of last 6 months, and avg of last 12 months. What is the best way to go about this? I tried grouping the data, but the groups can't overlap (ie 4/20/22 would need to be included in last month, last 6 months, and last 12 months. I also tried this formula Last month = CALCULATE('Charge Data'[Total Charges],dateadd('Charge Data'[Date Satisfied],-1,MONTH)) But adding that measure to the matrix values just includes it as a new row... If that makes any sense? Any and all help is appreciated!Create miscellaneous/other group using count below a threshold
I've got a Line and Stacked Column Chart (using this because of Pareto line). I used to create a measure "Count". Count = CALCULATE ( COUNTROWS ( Projects ), ALLSELECTED ( Projects ), VALUES ( Projects[Points] ) ) "Project[Points]" is the Shared axis and "Count" is the Column values. Now I want to use this measure and combine any calculated values below 3 into a miscellaneous/other group. So it would be removing "M" thru "g", and creating an "other", stacking them all together. I've been using power bi for about 2 months now. So the more steps explained the better. Thank you.
