DistinctCount ONLY the lowest values of a column in case of multiple rows
I'm looking for a DAX calculation which does DISTINCTCOUNT with a certain condition. If a Customer has two different Priorities, it should only do a count on the lowest Priority. To demonstrate with dummy data: Customer Priority Jan 5 Tom 2 Dirk 6 Jan 1 You see that Customer Jan has two rows, one with Priority 5 and 1. In this case I want to DISTINCTCOUNT the Customer only for the lowest Priority, being 1 in this case. The end result should look as follows. Thanks in advance!!
Filter and Replace values
Hi guys, I want to filter and replace the SUM of the Distinctcount values when Admin & physical both apply together and replace the total which would be 2 with 1. Please note, this is only an extract of the dataset. I already did the DistinctCount before with this measure: DistinctCountInspectionCategory = DISTINCTCOUNTNOBLANK('Inspections'[Inspection Group]) Sorry I couldn't paste all the values, but the relavent two ID's are included. Thanks for your help. Trip Inspection Id Inspection Group DistinctCountInspectionCategory 9090469 Administrative 1 600384 Administrative 1 600480 Administrative 1 600575 Administrative 1 600671 Administrative 1 600767 Administrative 1 793963 Administrative 1 793997 Administrative 1 794182 Administrative 1 794267 Administrative 1 826837 Administrative 1 829543 Administrative 1 829543 Physical 1 829588 Administrative 1 829588 Physical 1 928621 Administrative 1 928648 Administrative 1 928675 Physical 1 953030 Administrative 1 953104 Physical 1Count distinct start dates by item
Dear all, I have an issue with counting all distinct start dates per item (id). f.e. of the data ID date start or stop date 1 12/02/2024 start 1 14/02/2024 stop 1 15/02/2024 start 1 16/02/2024 stop 2 05/03/2024 start 2 05/03/2024 stop 2 05/03/2024 start 2 05/03/2024 stop 3 10/06/2024 start 3 10/06/2024 stop 4 10/06/2024 start 4 10/06/2024 stop i am trying to make a table that has following info (only the start dates per item - and total amount of start dates that each item has) ID test date start or stop date count amount of days tested 1 12/02/2024 start 2 1 15/02/2024 start 2 2 05/03/2024 start 1 3 10/06/2024 start 1 4 10/06/2024 start 1 I dont really need to start or stop column but i kept it here as en example. now i only have "1" everytime i try to calculate how many distinct dates there are, but if i remove the date column it does calculate correctly the distinctdates, with filter that start stop column = start. Count amount of days tested = CALCULATE ( DISTINCTCOUNT ( 'table'[test date] ) , FILTER ('table', 'table'[start or stop] = "start") ) Afterwards I want to be able to make a percentage with all the ID that have more then 1 (or only 1 test date) test date over ( / ) all ID in total. So i can see a percentage of how many ID were only tested once. how should change this calculation? kind regards
DistinctCount , ignoring one filter
Hi, Say I am working on the datamodel found in this report : https://tinyurl.com/2upv9t53 I want to create a similar table to this, where distinct count of customerIds are displayed for all rows (in this case it would be the number of customerIds in the category group, and ignore all filters on the customers table. So the expected outcome would be something like this: I have tried the formula in the screenshot below, but it seems to give me a cartesian product of some sort. Here is the Customer table showing its mapping with the customergroup table:Distinct Count based on calculated percentage measure
I have a table with hundreds of thousands of rows for individual sales, but each sale is designated to a specific group. I have a slicer that selects the desired reporting period (end date) and then I can calculate the total sales for the rolling-12 months based on the selected date both for the national total and each group. Finally, I have created a measure that shows the percent contribution to the nation for each group during the selected time period (Group Sales %). This all works great. What I'm struggling with is getting a simple distinct count of the number of groups whose percent contribution (Group Sales %) is > 1%. See the screenshot below which shows the list of group sales percents, and the distinct count measure I've written. You can see that it's counting each group once, without being filtered by the measure. How can I change this measure to correctly count ONLY the groups where Group Sales % >= .01? The resulting card should show 19, not 92 (which is the count of ALL groups).Get column total and percent of column
I created a matrix of 2 categories and an ID column - the ID column will be used in the "Value" but as COUNT DISTINCT. I want a measure to insert in between all columns of the matrix that show the % of each row Example: ColumnB Item1 % of Total ColumnA Item1 4 4/13 = 30.77% ColumnA Item2 9 9/13 = 69.23% Total 13 100% Here is what I've tried and my values are grouped by ColumnA, ColumnB, and date. % of Volume = VAR totalbyOrigin = SUMMARIZE ( TableA, TableA[ColumnA], TableA[DateCreated], TableA[ColumnB], "% of Volume", DISTINCTCOUNT ( TableA[ID] ) ) RETURN DIVIDE ( totalbyOrigin, CALCULATE ( DISTINCTCOUNT ( TableA[ID] ), ALLSELECTED () ) ) I get the following error message: The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.
Correlate two separate date fields in Dax for %Resolved and visuals
Hi, first time poster! I usually just muddle through and keep trying over and over until I get things cobbled together, but this one is a bit more complicated than ones I've dealt with in the past. We try to avoid competing values in the same table (2 dates used interchangeably, 2 names used interchangeably) but we couldn't avoid it this time. PBIX File: Tickets_resolved_test.pbix Excel File: PBI_Ticket_resolved_sanitized.xlsx Current formulas: TicketsWorked = DISTINCTCOUNT(Table5[Ticket_Number]) TicketsResolved = CALCULATE( DISTINCTCOUNT([Ticket_Number]), Table5[resolved_by]=SELECTEDVALUE(Table5[member_id]), Table5[date_resolved]=SELECTEDVALUE(Table5[date_worked]) ) %TicketsResolved = [TicketsResolved]/[TicketsWorked] Goals: 1. Find % of tickets resolved by each selected member(s) of the team during a selected time range 2. Allow you to select different dates on a slicer 3. Show table of member_id, # TicketsWorked, # TicketsResolved, and %Resolved 4. Line chart view of %Resolved per member_id over time 4. When you select multiple of member_id in a slicer, you get totals for TicketsWorked, TicketsResolved, and maybe average of %Resolved. Also just fine with totals for the first two. Issues: 1. Sometimes a ticket can be worked on by multiple people but each ticket can only resolved by 1 person. This results in duplicate rows for the "ticket was worked on" set, but each ticket only has 1 distinct value for resolved_by. There are other columns not listed in this file (such as their time entry notes, start time and end time for that time they worked the ticket, etc) that are unique and/or distinct per row, so we can't de-duplicate rows. 2. A ticket isn't necessarily closed on the same day it was opened, which means you HAVE to relate date_resolved to date_worked. If you try it without that it just checks if the ticket was worked on in the time range selected, then checks if the ticket was resolved by a secl 3. This table 2 distinct sets of data revolving around ticket_number: "this ticket_number was worked by this member_id during this datetime_worked" and "this ticket_number was resolved by this resolved_by at this datetime_resolved". What I've done so far: 1. Finding the number of tickets worked is easy, you just count the number of distinct tickets per member at any given time range. 2. I thought that finding the number of tickets resolved would be just as easy, but I'm having difficulties relating resolved_by to member_id, and date_resolved to date_worked, we don't usually have competing similar values in the same table. 3. In another file of ours, we used X=SelectedValue(Y) to relate things like this, but that method isn't working here, at least for the dates. I've also run into issues with there being multiple dates (or the full column of dates) referenced in DAX which I've tried to resolve with MAX(DateColumn). As you can see, no issues with TicketsWorked, but once I try to correlate date_resolved to date_worked in the TicketsResolved DAX then I run into issues. If I just correlate resolved_by to member_id in TicketsResolved Dax, it gives me a number but also counts tickets that were closed after the selected date as well. Thanks in advance for any help offered.DistinctCount shows 1 when it is 0
I have added a Card to my PowerBI report that shows how many distinct customers are matched, per Reason code. I have 8 lines for 4 customers. so with no filters the card shows 4, which is correct. If I filter on Reason=Material, the card shows 3, which is also correct. And when Reason=Process, the card shows one, which is also correct. If I exclude Material and Process from the filter, the card should show 0, but shows 1. Why??? And more importantly, how do I fix it? /*CountOfMatched = DISTINCTCOUNT(Table[Customer])+0*/ CountOfMatched for Reason Material = 3 (correct) CountOfMatched for Process = 1 (correct) CountOfMatched for none of them = 0 but the visual shows 1
