dax code for calculated measures

3 Topics

Data Definition Tooltip
Hey everyone, hope you're all doing well! I'm working on a technical task and I'd really appreciate your help and insights. My goal is to create a dynamic glossary for a data report that will be utilized within the tooltip. I've built a measure that checks if a specific field from 'table a' is currently selected in a visual using ISINSCOPE(). If it is, the measure returns TRUE. Then, I use an IF statement to take that column's name and use LOOKUPVALUE() to get its definition from another table, 'table b'. Here's an example of the DAX code I'm using. I've applied this iteratively for each column: VAR ActiveColumnListRaw = IF( ISINSCOPE('table a'[column 1]), LOOKUPVALUE('table b'[definition], 'table b'[Field], "column 1", "Definition Not Found") & UNICHAR(10) & UNICHAR(10), "" ) VAR FinalFormattedList = IF( LEN(ActiveColumnListRaw) > 0 LEFT(ActiveColumnListRaw, LEN(ActiveColumnListRaw) - 2), //remove the last two unichar(10) BLANK() ) RETURN IF( LEN(TRIM(FinalFormattedList)) = 0, "No Listed Column In Scope", FinalFormattedList Just a quick note: I'm using CHAR(10) to handle cases where multiple fields are selected. Also, I'm working with a live data connection, so I can't add any new calculated fields or tables. The measure works great until I use one of these fields on the y-axis of a visual, like a clustered column chart. When the data is aggregated, the ISINSCOPE() function stops working. This is where I'm stuck. I have two main questions for you all: Would using CONTAINSSTRING() to check for the column name within ISINSCOPE() solve this issue? If that approach doesn't work, what are some other solutions I could try? Thanks a ton in advance for your help!
Solved
alfaraj_ar
1 year ago Place DAX Commands and Tips
1.1KViews
0likes
3Comments
Percentage of difference between previous quarters
Hi, I am trying to create a measure that calculates the percentage of difference between my previous quarters (all in the same year), so basically Q4 vs Q3, Q3 vs Q2 and Q2 vs Q1. I created the following measure but it only gives me 0. I created the different variables separatley and also changing the quarters type of data, cause I have it as text, like Q1 2025, Q2 2025, Q3 2025, and Q4 2025. What I am also not sure if I did right is about using the Q Patients measure, which is a measure that takes the current patients from the Product and Quarter selected on the filter in the page PercentageChange = VAR SelectedQuarter = SELECTEDVALUE('Total Patients Quarters'[Quarters]) VAR SelectedProduct = SELECTEDVALUE('FY 2025'[Product]) VAR CurrentQtr = VALUE(MID(SelectedQuarter, 2, 1)) VAR PreviousQtr = IF(CurrentQtr = 1, 4, CurrentQtr - 1) VAR PQ = "Q" & PreviousQtr VAR CurrentPatients = [Q Patients] VAR PreviousPatients = CALCULATE( [Q Patients], 'FY 2025'[Product] = SelectedProduct, 'Total Patients Quarters'[Quarters] = PQ ) RETURN IF( ISBLANK(PreviousPatients), BLANK(), DIVIDE(CurrentPatients - PreviousPatients, PreviousPatients, 0) ) Hope someone can help me out Thanks, Silvia
Solved
Silvia_GB
1 year ago Place DAX Commands and Tips
1.7KViews
0likes
7Comments
Secondary y-axis: calculated average, how not to display those categories in primary axis & legend
I have created a measure to calculate a monthly average for years 2018 and 2019 that I would like to visualize on the secondary axis to compare to monthly counts for more current years (2020-2024) but I don't want the bars for those years (2018-2019) included in the legend/visualized on the graph. However if I filter to exclude those years, my average line also disappears. I have tried to use ALL() and REMOVEFILTERS() on both the counts and the average measures but no dice. I'm sure someone here has a better idea! Thank you!
Solved
akt
2 years ago Place DAX Commands and Tips
661Views
0likes
1Comment