Iterating a distinctcount between two tables

DAX experts,

I am struggling with this concept and am need of some support. I have two tables that have a one-many relationship, table 1 being one and table2 being many. I am attempting to filter down table2 with a specific column in table1 and perform a distinctcount on the rows in table2. Once complete, I want to change the context back to the next row in table1 and perform the same count for the new table2. This iteration needs to take place for table1 and once complete, I need to sum up all of the distinctcounts.

Does anyone know if Sam has worked through a similar example and if so, can you tell me which course it is? Once I get back to my computer, I will share a screenshot of my model and Dax formula I’m using.

Thank

Place a screenshot and the current formula and I think I’ll be able to solve this relatively quickly for you. I just want to make I totally understand your requirement. Thanks

Hello Sam,

I ended up sleeping on this problem and found a solution early morning; see below screenshots as a reference. Screenshot 1 is showing references to table 1 and table 2. Screenshot #2 is showing the table visualization where wellname is coming from table 1 and the servicecompany & refno columns are coming from table2. As a note, i am using the userelationship function to bring servicecompany over to table2.

I was having an issue with “sub-events” being counted individually so i created the calculated column “2refno1” which allowed me to round down the value to the nearest whole number.

At this point, i wanted to count the rows while jumping back/fourth between tables. My first attempt at doing this, i created the measure “1refnocount” and the expression is shown below. At my 2nd attempt, i created the measure “1refnocount2” and looks like the total count is lining up now.

let me know what you think, thanks for reaching out so quickly.

``````1RefNoCount =
COUNTROWS(FILTER(wvJOBINTERVALPROBLEM, wvJOBINTERVALPROBLEM[2REFNO1]))

1RefNoCount2 =
SUMX(
CALCULATE(DISTINCTCOUNT(wvJOBINTERVALPROBLEM[2REFNO1])
)
)
``````

screenshot #1

I am restricted to only one image per post, see below screenshot #2

Also this is the expression i used for my “2refno1” column. I was having issues because this column contains both text and numbers and therefore could not simply change the data type so i put together this if statement.

``````2REFNO1 =
if(
iserror(rounddown(value(wvJOBINTERVALPROBLEM[REFNO]),-1)),
00,
rounddown(value(wvJOBINTERVALPROBLEM[REFNO]),0.1)
)
``````

screenshot #2

Ok glad you found a solution.

Just looking at your model though…I certainly feel you can make this simpler.

Make sure to turn of the bi-directional relationships. You shouldn’t need these.

I’m also not sure why you need the inactive relationship also.

Check out all my best practise tips within this course.

It’s essential to make it more intuitive in the model. Even when I look at it the way it is I get a bit confused.

Thanks Sam. I’ve gone through trial and error with this model. Ultimately I was able to turn a 600mb file down to 120mb with some parameters and merges. Regarding the inactive relationships, I have unique foreign identifiers located in my tables so I have them visible in case I want to call upon them. I can probably start a new topic just on this but I’ll go through your videos first.

Thanks for the help