User Profile
bangsheadondesk
Frequent Visitor
Joined 3 years ago
User Widgets
Contributions
Limit Summarized Table Size
I need to count total and average program counts, both at the individual record level ([Opp Id]) and as a total group (all selected records from the 'Opportunity' table). This requires a DISTINCTCOUNT of [Program Name] at the individual Opp Id level, then adding those together when multiple [Opp Id]s are selected. (ie--If one person interacts multiple times with one program, it should only count 1 time. But if two separate people interact with the same [Program Name], it should be counted twice in the final calculation.) I have a working measure: Campaign Touches by Opp = SUMX( SUMMARIZE( Opportunity, Opportunity[Opp ID], "UniqueActivityCount", CALCULATE(DISTINCTCOUNT(ACTIVITIES[Program Name]), USERELATIONSHIP(Opportunity[Contact Id], LEADS[Contact Id]), CROSSFILTER('Date Opp'[Date], Opportunity[Created Date], OneWay))), [UniqueActivityCount]) The first problem is table size. The final report will be primarily direct query from two sources (Snowflake and Salesforce), but charts will usually still involve millions of lines at a time. I'm building with only a limited subset of data, and if only a few records are selected, the measure is fine. If even one month's worth of the full dataset is called at once, the time needed to compile is staggering. The second problem is the complexity of the relationships. The report has multiple components, so finding the best combination of active/inactive relationships to work well across all pages, with and without measures, is frustrating. The data is also filthy (the worst I've ever seen), and full of errors/empty fields/duplicates, which require careful cross-filtering. I want an efficient way to limit the SUMMARIZE function to just the filtered [Opp Id]s on the page. I have tried both adjusting the current measure and introducing a summarized table as a variable for the measure using ALLEXCEPT, but that is complicated given the many other filters to work around. Doing the same thing with ALLSELECTED sums everything on individual records or brings in far more data than needed. Would KEEPFILTERS work somehow? Is there any way to streamline my current measure, or can you recommend a better option?Re: How to append only specified columns?
Appending as new (otherwise you could start at second step and add to an existing query): let Source = Table.SelectColumns(Table1,{"Column1"}), #"Appended Query" = Table.Combine({Source, Table.SelectColumns(Table2,{"MatchingColumn"})}), #"Removed Duplicates" = Table.Distinct(#"AppendedQuery", {"Column1"}) in #"Removed Duplicates" You can test it with an index column (sort descending).Re: BUG: Formatting options are unavailable for this visual
SAME. Sometimes it's an issue, sometimes it's fine. Sometimes it bounces back and forth, unavailable then randomly available again. No pattern whatsoever. Sometimes grouped, sometimes not. Ungrouping isn't always a solution. Sometimes changing tabs then back again will fix, sometimes no. Sometimes I can duplicate the page and change it there. Sometimes the duplicate doesn't work and the original suddenly does again... WHAT IS GOING ON???Dynamic MTD target by chart and card
Hi, I am trying to craft visuals that will a) show what the mtd target should be based on what day of the month is selected from slicer or filtered value from another chart (need for both card and column in a larger chart) b) display as a reference line for where we should be at in the month in order to hit targets (line chart) c) be accurate for current and historical months, depending on what is selected in date slicer I have a date table with fields (among many others) 'date'[date] (date/time format) CALENDARAUTO ( 6 ), AND ( [date] >= DATE ( 2020, 7, 1 ), [date] <= MAX ( 'Opportunity'[Close Date] ) 'date'[CD] (for calendar day, whole number format) "CD", DAY([Date]), and 'Target' Table built in Power Query with fields [Date], [Days in Month], [Leads Target], and [Leads Target by Day] (formula: each [Leads Target] / [Days in Month]) They're joined on 'Date'[Date] to 'Target'[Date] Other tables include a date2 (joined on [date], and target[date]) that I tried using to give me a 2nd table for a lookupvalue option (didn't work, obviously) I've tried this, which works in a card and gives either max goal for month or goalmtd as of today, as long as only a single month date range is selected: DG Works = var dailygoal = calculate(sum('Target'[Leads Target by Day]), USERELATIONSHIP('Date'[Date],'Target'[Date])) var maxdate = CALCULATE(max('Date'[CD]), ALLSELECTED('Date'[Date])) var dayofperiod = CALCULATE(LOOKUPVALUE('Date'[CD],'Date'[Date], TODAY())) var testdailygoal = if(SELECTEDVALUE('Date'[Mo Diff]) = 0, dayofperiod, maxdate) return CALCULATE(dailygoal * testdailygoal, ALLSELECTED('Date'[Date])) works card, gives either max goal for month or goalmtd as of today But it doesn't work historically, and it doesn't work in a line chart with CD on the x-axis. (I need the x-axis to be CD, because this has to tie into other overlapping charts.) Plus, since one requirement is either the ability to select a day from the chart and get the dynamic mtd goal for that point in the month or a static report that shows mtd goal target for end of month and the mtd target frozen at halfway through month, this isn't sufficient. I have also tried a whole host of other things that won't work. I won't bother posting them, but some--certainly not all--are in the sample file. (Others were deleted in frustration--just trust me, there were lots of attempts.) I'm sure I'm probably missing something silly, but my brain is fried and not working well at this point. It may require two separate measures, one for chart and another for card. Looks like my security filters won't allow me to upload the sample file I recreated. I'll see if there's a workaround, but posting in meantime. Please help?
