Distinct Count of ID # based on filters in multiple columns

[dodonald]

I am looking to calculate the "Right First Time" from below data.  I'm sure this is so simple but for some reason it keeps escaping me.

I want to count the unique # of IDs that do not have any instances of "rework" across tasks X, Y, and Z (excluding Q) and Role A, B, and C (excluding M). In this case, my count of Right First Time is 3 because ID # 789 does not have any value of "Rework", 987 only has rework from Task Q, and 654 only has rework by Role M.

I have measures already set up to count the total number of rework votes with the exclusions above, but for some reason I cant figure out how to get the unique count of the ID #s where the total number of rework votes = 0.

ID #	Disposition	Role	Task Name
123	Rework	A	X
123	Proceed	A	X
123	Proceed	B	Y
123	Rework	C	Z
123	Proceed	C	Z
456	Proceed	A	X
456	Proceed	B	Y
456	Rework	C	Z
456	Proceed	C	Z
789	Proceed	A	W
789	Proceed	A	Q
789	Proceed	B	Y
789	Proceed	C	Z
987	Proceed	A	W
987	Rework	A	Q
987	Proceed	A	Q
987	Proceed	C	Z
654	Rework	M	W
654	Proceed	B	Y
654	Proceed	C	Z

Appreciate the help!!

[ThxAlot]

UnicTask.pbix

[rubayatyasmin]

Hi, @dodonald 

To calculate the "Right First Time" count based on the given data, you'll need to follow these steps:

1. Exclude the rows where Task Name is "Q" and Role is "M."
2. Group the remaining data by ID #.
3. For each group (ID #), check if any "Rework" value exists in the Disposition column for tasks X, Y, and Z.
4. Count the unique ID #s where the total number of "Rework" votes is zero.

[dodonald]

How do I do #2? I dont want to transform the whole table b/c I have at least 25 measures and several visualizations dependent on how the entire table is currently structured.

[rubayatyasmin]

1. Open Power Query Editor:

   • With the data loaded into Power BI, navigate to the "Home" tab, and click on "Edit Queries." This will open the Power Query Editor.

2. Filter Rows:

   • In the Power Query Editor, select the "Task Name" column header.
   • Click on the "Filter Rows" button in the "Home" tab.
   • In the filter dialog, uncheck the box for "Q" in the list of Task Names, and click "OK" to apply the filter.

3. Add Another Filter:

   • Now, go back to the Power Query Editor and select the "Role" column header.
   • Click on the "Filter Rows" button again.
   • In the filter dialog, uncheck the box for "M" in the list of Roles, and click "OK" to apply the filter.

[dodonald]

I dont want to do this at the query level. This query is HUGE (much more than the data above) and I have several other measures and visualizations already in place.

[Idrissshatila]

Hello @dodonald ,

check this solution https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Count-distinct-rows-based-off-one-co...

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin

[dodonald]

I guess I'm not sure how to use "Filter". Can you possibly walk me through how this linked solution relates to my data?

Sorry I'm being dense. I've been working with 2 big data sets all week and - as it's now Friday afternoon - my brain is mush and my eyes are swimming. 😵

[rubayatyasmin]

in PQ just click on the dropdown of the column name then select the value you want to keep and uncheck all the other values. this how filtering is done in PQ. 

[dodonald]

Your responses are not helpful. You continue to not read my replies. I am looking for a MEASURE not a filter within PQ. I know how to filter a column with a dropdown. I am looking for help with the Filter DAX.