Add row/filter context to SUMMARIZECOLUMNS measure
Hi, I have the following measure, which works on a total level, but not in a table visual with fields from another table (dimCampaigns): Unique Opened = CALCULATE ( COUNTROWS ( SUMMARIZECOLUMNS( fctActivities[CONTACT_ID], fctActivities[CAMPAIGNID], fctActivities[SUBJECTLINE], "CountEmailOpen", CALCULATE ( COUNTROWS ( fctActivities ), fctActivities[ACTIVITYTYPE] = "EmailOpen" ) ) ) ) This is probably because SUMMARIZECOLUMNS doesn't have a row context (as stated by this article). So I figured I would have to somehow retrieve such a value from the filter context of the table visual containing data from 'dimCampaigns' into the measure manually (by using VALUES). So I created the following: Unique Opened = CALCULATE ( COUNTROWS ( SUMMARIZECOLUMNS( fctActivities[CONTACT_ID], fctActivities[CAMPAIGNID], fctActivities[SUBJECTLINE], "CountEmailOpen", CALCULATE ( COUNTROWS ( fctActivities ), fctActivities[ACTIVITYTYPE] = "EmailOpen", VALUES(dimCampaigns[CAMPAIGNCATEGORY]), VALUES(dimCampaigns[NAME]) ) ) ) ) This still works on the total level, but still not in the table visual. It's the following table visual where I would like to add the measure to, but if I use the above measure it gives the dreaded error "SummarizeColumns() and AddMissingItems() may not be used in this context.": I think I'm close, but I just cannot get it to work. Any help would be much appreciated, thank you!function Values doesn t return Table of values but error
Hi, I want to use VALUES function and study from https://dax.guide/values/ that input is table/column and output table (one or more columns) I tried to od it on most simplest case but it failed, can sombebody told me why ? everything seems OK based on DAX guide, what I missed ? Current Data Model My DAX formula Measure = VALUES(Locations[Location]) When I tried to test it on table Measure = VALUES(Locations) I got another error "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value." I am totally confused, because there is only one condition that input parameter must be table or column, any ideas what I missed ? (Don t think so that DAX.guide is wrong in description) thanks in advance!
Count Unique values between a date range
Hey, Had to share this as i've been trying to figure it out for a week now. Finaly got it. So the intention of the below is to add a column in that defines if the row is between the start data and end date, adding a "Yes/No" where true or false. It then filters the table on "Yes" and counts the unique values in a specific coulmn. Where i have used this is to count the tickets raised last month and then compare to the tickets raised the previous month to show a specific increase or decrease in ticket numbers. This took me ages to figure out, hopefully helps someone else! Unique Count = VAR StartDate = EOMONTH(TODAY(),-2)+1 VAR EndDate = EOMONTH(TODAY(),-1) RETURN CALCULATE( DISTINCTCOUNT(Table1[Refference]), Filter( ADDCOLUMNS( Table1, "Last Month", IF(Table1[CLOSE_TIME_Month_Year] >= StartDate && Table1[Table1] <= EndDate, "Yes", "No")),[Last Month] = "Yes"))
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.IF value exists on calendar date
Hi everyone, i need help creating a measure that evaluates whether a value in on table exists at a particular date within the calender table . I am working with Two tables, on main table that has a customer and a purchase date. The second table is just a table showing the daily calendar dates. the outcome wanted is to evaluate whether a purchase was made by a customer at a certain date on the daily calender. the tables are as shown below : RESULT If a customer has purchased something on the date equivalent to the calendar date, return true, else false. as shown below: If anyone could assist me with writing a measure that could produce this outcome that would be wonderful 🙂 all help and sugegestions are welcome: Thank yousum values of a list of unique values
Hello, I'm having trouble displaying a list of unique values for a second line of code that sum only the Lbs of values in that list, here is my code: Total LT = VAR list = CALCULATE(VALUES('DATA BASE'[OP]),'DATA BASE'[EE] = "LT") RETURN CALCULATE(SUM('DATA BASE'[Lbs]),'DATA BASE'[OP]=list) here's a example of my data base (is a lot more bigger): i want the list of the OP's that has some value in EE = LT, for later sum the total Lbs of that OP Thanks
