calculated columns

71 Topics

Created a DAX Query Tutorial Video on Youtube
Just dropped a new video on writing DAX queries in Power BI! https://youtu.be/T_-Z9Mstygk?si=IC1QrA6E3iGWdCJH If you're diving into data analysis or trying to level up your dashboard game, understanding DAX is a must. In this video, I break down: What DAX is and why it’s powerful 🔹 The difference between calculated columns and measures 🔹 How to write clean, efficient queries 🔹 Real-world use cases that make your dashboards smarter Whether you're just starting with Power BI or looking to optimize your reports, this guide covers it all with simple explanations and practical examples.
Solved
HusainCementwal
1 year ago Place DAX Commands and Tips
3KViews
1like
4Comments
Calculated Values in a Chart
Below is a table that I built in SQL using a cross join for each customer visit-priorService, where we provide an "as of" list of prior services done for each customer visit to a store. At visit 1, customer A has only one prior high-level service done on 1/1/2006. At visit 2, that same customer has two prior services done, S1 high and S2 medium. Finally, at visit 3, that same customer has four prior services done, S1, S2, S3, and S4. It is possible for a customer to have no prior services done, as indicated by customer D. This table is only part of a star-schema where my dashboard is centered on visits, so an entry in this table NEEDS to be recorded for each visit in the table below to identify that that visit for customer D is associated with 0 prior services. Each prior service has a severity and date (as well as other fields not listed in the table I have already created below). "Priors" Table: Customer VisitID Visit Date PriorServiceID PriorServiceDate PriorServiceSeverity A V1 1/1/21 S1 1/1/06 High A V2 6/1/21 S1 1/1/06 High A V2 6/1/21 S2 3/1/21 Medium A V3 8/1/21 S1 1/1/06 High A V3 8/1/21 S2 3/1/21 Medium A V3 8/1/21 S3 7/15/21 Low A V3 8/1/21 S4 7/17/21 Medium B V5 7/1/22 S5 1/1/01 High B V5 7/1/22 S6 1/10/01 Medium C V6 1/1/21 S7 1/1/98 Medium C V6 1/1/21 S8 6/1/05 Low C V7 9/1/21 S7 1/1/98 Medium C V7 9/1/21 S8 6/1/05 Low C V7 9/1/21 S9 1/1/21 High D V8 10/1/21 NULL NULL NULL The dashboard I've created has slicers on visit date, prior service date, and prior service severity. I want to chart with the # of FILTERABLE prior services on the x-axis, and the number of visits on the y-axis. To achieve this, I initially built the following summary table separately in my data model. Priors Counting Table = SUMMARIZE( 'Priors', Priors[VisitID], "PriorVisitCnt", DISTINCTCOUNTNOBLANK( 'Priors'[PriorServiceID] ) + 0 ) I linked this to my main visit-level table using the VisitID and got the chart I wanted, but the chart becomes meaningless when I filter on the service date or service type. Without any restrictions, there are upto 190 prior services done for a customer, but we need to see only the prior services done in 2017 when I filter on that service year for instance. It still shows 190 when I select 2017 on the slicer because there is a customer who had a grand total of 190 visits between 2000 (the oldest service recorded) and 2024 whose total amount of services keep showing. Based on my review of similar posts on this forum, it looks like measures can't be thrown into charts so easily but there COULD BE a workaround through calculated columns? I'm struggling a lot on this to even get the count of prior services per EACH visit on a measure to begin with. I already report on the min, max, median, and mean number of prior services done as of each visit for a customer, and it's very easy using a measure such as below. So I'm HOPEFUL something like this can be done at the chart-level? Median = VAR Summary = SUMMARIZE('Priors', Priors[VisitID], "PriorVisitCnt", DISTINCTCOUNTNOBLANK( [PriorServiceID] ) + 0 ) RETURN MEDIANX ( Summary, [PowerBICnt] )
Solved
SoupyNoodles14
1 year ago Place DAX Commands and Tips
1.1KViews
0likes
5Comments
How to fix Date X Axis?
Hello! To give context I am trying to count the number of transactions done in established periods such as (Actual month or Past month). For this, I used calculated measures: Actual Month = VAR EARLIESTYEAR = YEAR(MAX(Transaccional[Date])) VAR EARLIESTMONTH = MONTH(MAX(Transaccional[Date])) VAR EARLIESTDAY = DAY(MAX(Transaccional[Date])) RETURN CALCULATE(count(Transaccional[TransactionId]), FILTER(Transaccional, YEAR([Date])=EARLIESTYEAR && MONTH([Date])=EARLIESTMONTH && DAY([Date])<=EARLIESTDAY )) Interanual Month = CALCULATE( count(Transaccional[TransactionId]), FILTER(Transaccional, YEAR([Date])=YEAR(MAX([Date]))-1 && MONTH([Date])= MONTH(MAX([Date])) && if(MAX([Date])<>EOMONTH(MAX([Date]),0),Day(Transaccional[Date])<=DAY(MAX([Date])) ,DAY([Date])>=DAY(MAX([Date])) || DAY([Date])<=DAY(MAX([Date]))) )) Previous month = CALCULATE( count(Transaccional[TransactionId]), FILTER(Transaccional, YEAR([Date])=YEAR(MAX([Date])) && MONTH([Date])= MONTH(MAX([Date]))-1 && if(MAX([Date])<>EOMONTH(MAX([Date]),0),Day(Transaccional[Date])<=DAY(MAX([Date])) ,DAY([Date])>=DAY(MAX([Date])) || DAY([Date])<=DAY(MAX([Date]))) )) The purpose of this formula is to make the data visualization dynamic since I want the user to filter the data, and depending on those changes I want the graph to change. I am certain that the formulas work just fine. Still, when I have to graph the measures, the X axis corresponding to the date (specifically DAY), the numbers are wrongly distributed along the X axis. For example: The latest date on this data is (6/19/2024) but the measure shows values up to day 30, which means that the data is not well aligned. I have tried and thought this a lot but don't know what the solution would be.
drijo
2 years ago Place Report Server
550Views
0likes
2Comments
Site status classficiation based on different project status
Dear community: I have two tables one for site status, and the other table with project status by site where one site may have multiple projects. In the site status table I have a column for status, but I need this colums to be calculated based on a logic wich considers the different status for projects assigned to that site in the project status table. I have a relationship built for both tables using the site column. This is the logic: If at least one project is running, the site status is running (no matter is there are other projects with status closed or not started in the project status table) If all projects are closed, then the sites status is closed If all projects are not yet started then the site status is planned If there are no projects for a site, then the site status is not planned Here is an example: Project Status Table Project Site Status 1 A Running 2 A Closed 3 A Not Started 4 B Closed 5 B Closed 6 C Not started 7 C Not Started Then, using the logic, I shoud see the following values in the site status colum: Site Status A Running B Closed C Planned D Not Planned As site D is not listed in the project status table, the status for that site should be Not Planned Thanks in advance for your support!
Solved
Netope
2 years ago Place DAX Commands and Tips
813Views
0likes
3Comments
Convert Calculated Column to DAX Measure to detect one of two values
Hi all Previously from this thread, https://community.fabric.microsoft.com/t5/Desktop/How-to-find-duplicate-values-in-one-column-while-another-column/m-p/3894349 Thanks to one for the members, Ashish_Mathur to create a way to find the duplicate values through the formula shown below. Column = if(CALCULATE(DISTINCTCOUNT(Scanned_Item_Two_Controllers[Scan_Controller_Node_Id]),FILTER(Scanned_Item_Two_Controllers,Scanned_Item_Two_Controllers[Page_Barcode]=EARLIER(Scanned_Item_Two_Controllers[Page_Barcode])))>1,"Duplicate Found!","Unique") I have created another new measure that represent what I want to do where I can detect, 'Duplicate Found!' Filter Value = CALCULATE( COUNTA('Scanned_Item_Two_Controllers'[Column]), 'Scanned_Item_Two_Controllers'[Column] IN { "Duplicate Found!" } ) I hope to get more insights on this matter. Thank you for reading this thread.
Solved
JonKho
2 years ago Place DAX Commands and Tips
714Views
0likes
3Comments
Calculated columns for previous weeks values
Hi guys, I have a long table of weekly shares (Share) of several products (SKU) in different regions (Region) as below with the yellow column names. I wish to add 3 calculated columns which present the previous week (-1 WEEK) share of the same product in the same region - same thing for the week before (-2 WEEK) and before that (-3 WEEK). Please help. Thanks
Solved
akfir
2 years ago Place DAX Commands and Tips
651Views
0likes
2Comments
Error when subtracting a calculated column with a measure
Hello. I have an issue where I have a column, lets call it "Gap", that takes a measure ("Measure A") and subtracts a calculated column, ("Column B"). When I do this, the calculation that I am looking for is incorrect. When I put in: Gap =Measure A-Column B and Measure A=24 Column B= 20 So the answer would be 24-20=4. However the answer Power BI is giving me is -19. Why is the table that I have these columns in showing the gap column to have -19. All other calculations are incorrect. Could you please explain to me why its doing this when I have a Measure be subtracted by a column? The reason I have a Measure in the first place, is because I have it synced with a slider to allow the user to change around the weights. Please let me know
Anonymous
2 years ago Place DAX Commands and Tips
765Views
0likes
3Comments
count of previous entries
I have tried to get help with this solution before and may not have worded it correctly, so trying again. I have a table where I need to add two calculated columns. Ive called them Calculation 1 and calculation 2 here is the table Date Name ID Location Calculation 1 Calculation 2 01/11/2023 Allan A1 London 0 0 02/11/2023 Allan A1 London 1 1 03/11/2023 Allan A1 Glasgow 0 2 04/11/2023 Allan A1 London 2 3 05/11/2023 John A2 London 0 0 06/11/2023 Allan A1 London 3 4 Using the ID field as a key field, I am trying to achieve the following. Calculation 1 to be a count of previous times that ID has been at that location. Calculation 2 to be a count of previous times that ID has been at all locations. For example, on 4/11/23, ID1 (Allan) has been to London twice previously, so the entry is 2 in the calculation 1 column, however he has also been to Glasgow on one occaision, therefore, calculation 2 has a count of 3. Twice for London plus one for Glasgow. The table will have approx 300000 entries, where there will individuals will not make visits for weeks. I have tried to count rows and been given suggestions here previously which, while grateful, have been unable to get working. These do need to be calculated columns as i need to be able to look at a particular status at the time of entry as it will also be linked to another table which will use the ID field and different dates. I appreciate any guidance or help. Lola
Solved
Anonymous
2 years ago Place DAX Commands and Tips
1.9KViews
0likes
3Comments
Calculated column with cumulative count of repeating dates
I have the following calculated table: I've been trying to add to that table a calculated column that runs a cumulative count of the repeating dates from the date column ([Data]) which should (obviously) reset back to 1 every time a new date comes by. The closest I could get was with the code below, but it still does not produce the results I want, which I hardcoded in red for better understanding of what I really need. Any help on this one is greatly appreciated!
Solved
leolapa_br
2 years ago Place DAX Commands and Tips
603Views
0likes
1Comment
Max date by group, rewrite measure to create calculated column
I have the following calculated table from this code: Tbl = SUMMARIZECOLUMNS( dAssets[Ticker], dDates[Date], "Purchases", [Shares purchased], "Sales", [Shares sold] ) Then I created the following measure that retrieves the last purchase date prior to each sale row... Last purchase date before respective sale = VAR Ticker_Ref = MAX( Tbl[Ticker] ) VAR Date_Ref = MAX( Tbl[Date] ) RETURN MAXX( FILTER( ALLSELECTED( Tbl ), [Ticker] = Ticker_Ref && [Date] <= Date_Ref && [Purchases] <> BLANK() ), [Date] ) ... which works just fine when dropped to a table visual: I tried to produce those same results above as a calculated column but I keep getting wrong results. How can I get that to work? Since I'm a DAX newbie, I still have a hard time reproducing a measure code as a calculated column or vice-versa... The closest I got was this... Last purchase date before respective sale = VAR Date_Ref = MAX( Tbl[Date] ) RETURN CALCULATE( MAX( Tbl[Date] ), ALLEXCEPT( Tbl, Tbl[Ticker] ), Tbl[Date] <= Date_Ref, Tbl[Purchases] <> BLANK() ) ... but it returns wrong results (max purchase row date per group of tickers, instead of max purchase row date within each group of tickers prior to or up to each respective sale row date): And before one suggests I should think long and hard before resorting to calculated columns as opposed to measures: I've already done such philosophical conundrum and realized that for this particular application, which is producing that piece of calculation, and others that follow, in order to reach a particular (and very complex) calculation goal, I have no other choice but go with calculated columns.
Solved
leolapa_br
2 years ago Place DAX Commands and Tips
605Views
0likes
1Comment