topic Re: Distinc count filter from different table in DAX Commands and Tips

Distinc count filter from different table

Tommyvhod — Thu, 27 Oct 2022 07:13:08 GMT

Hello All

I would like to re-create a measure what seems to be working in one reporting but not working in mine.

The formula is:

CALCULATE(DISTINCTCOUNT(Data[ID]), 'Production Plan'[PNCode] = VALUES(Data[PNCode]) )

The Production plan and data tables are not connected they have a dimension table together.

I tried to trim, clean and lower case the PNCode colums, but nothing seems to help. ( it seems that the filter part is not OK)

The problem is that the same ID is appearing on multiple operations but I want to filter only those which are on the same opeartion what was planned

SO same ID on operation 1, 2, 3 above one department but in planning we have only operation 1

And i would like to filter from data only unique ID with operation one. ( now the result is 3 because 1 ID 3 operations)

Thank you

Re: Distinc count filter from different table

ValtteriN — Thu, 27 Oct 2022 07:39:14 GMT

Hi,

Here is an alternative approach:

Data:

table (9)

table (10)

Dax:

Measure 20 =

SUMX(DISTINCT(FILTER('Table (9)','Table (9)'[Dim2] in VALUES('Table (10)'[Dim2]))),'Table (9)'[Value])

End result:

The result is 2 as expected since only values containing Dim2 = A1 are considered. This leaves 3 rows 2 of which are distinct.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

Re: Distinc count filter from different table

Tommyvhod — Thu, 27 Oct 2022 08:21:03 GMT

Thank you. It is working.