Distinct Count ignoring one column plus calculate average
Hello, all! I have the following table And im trying to get a simple DISTINCT COUNT[ID] but its not working because the column Type has two different values (B and E). If i use a mesure, it works BUT im trying to use a Column because after i get the distinct count, ill need get the average. Like the example below: In short, i need to get the average of distinct count[id]. Ive tried create a column with CALCULATE(DISTINCTCOUNT(Consult[id]) / DISTINCTCOUNT(Consult[date].[Month]), ALLSELECTED(Consult[date].[Day])) But its not working because its counting the same ID twice when the ID has the B and E type. If there is a way to get the column average that i need using a measure, ill be glad to learn how. Thanks!
Reoccurring Distinct Counts
Hello, I have a table that logs user activity across my dashboard that looks like this: My stakeholder has requested to get a count of distinct users who have viewed the report in this quarter and the previous quarter. I am able to show this easily in a table by adding flags for if they viewed in this quarter and the previous quarter for the quarter that is selected, then writing a measure to check if they submitted in both. Example below: However, the problem is I need to show this visual in a Card, not a table. In the example above my card would show the number 3, because there are only 3 users who have viewed the report in Q1 2023 and Q4 2022. I tried doing a cumulative count for users <= the selected quarter, however this returns all distinct users who viewed in either quarter. I also tried building a custom table with Summarize but there is no DistinctCountX function which throws a wrench in that plan. DAX Samples: RepeatUsers = COUNTX( FILTER ( SUMMARIZE ( 'Report views', 'Report views'[User], 'Report views'[Quarter], "Views", COUNT ( 'Report views'[UserID] ) ), [Views] > 0 && 'Report views'[Quarter] = SELECTEDVALUE(QuarterDim[Quarter]) ), 'Report views'[User] ) RepeatUsers = VAR SelectedQuarter = SELECTEDVALUE ( QuarterDim[Quarter] ) Var SelectedPriorQuarter= SELECTEDVALUE(QuarterDim[PriorQuarter]) VAR Result = CALCULATE ( DISTINCTCOUNT ( 'Report views'[UserId] ), FILTER ( 'Report views', ('Report views'[Quarter] <= SelectedQuarter && 'Report views'[YearTerm] >= SelectedPriorQuarter ) ) RETURN Result Any help is greatly appreciated!
