topic Re: count of common values - only using DAX in DAX Commands and Tips

count of common values - only using DAX

Greatbi1 — Sun, 14 Jun 2020 05:18:18 GMT

Hi All,

I have this is in continuation of Old post . An awsome solution was given by camargos88 using power query - join. Now I have realised that raw data size is huge and I need multiple charts and foreach combination I don't want to create join. I want to do it using DAX alone. Is it possible?

I have data like below:

State | Engineer|

Florida, Johny

Florida, Nick

Indiana, Johny

Indiana, Nick

I want a pivot table like below which shows common engineer between states.

State	Florida	Indiana
Florida	NA	1
Indiana	1	NA

Santhosh K

Re: count of common values - only using DAX

stevedep — Sun, 14 Jun 2020 08:09:59 GMT

Here you go:

In case you do not want to create a relationship between your base table the filter table:

EngineerCount = VAR EngineerCount = VALUES('Table'[Engineer]) VAR FilterEngineerCount = CALCULATETABLE(VALUES('Table'[Engineer]);ALL('Table');TREATAS(VALUES(FilterStates[State]); 'Table'[State])) RETURN COUNTROWS(INTERSECT(EngineerCount;FilterEngineerCount))

In case you can create an inactive relationship:

EngineersinBotStates = CALCULATE ( DISTINCTCOUNT ('Table'[Engineer] ); CALCULATETABLE ( SUMMARIZE ( 'Table';'Table'[Engineer] ); ALL ( 'Table'); USERELATIONSHIP ( 'Table'[State]; FilterStates[State] ) ) )

Which is based on this article:

https://www.daxpatterns.com/basket-analysis/

Result can be seen here, please note the data model and the use of a filtered states table, which is created with:

FilterStates = SUMMARIZE('Table';'Table'[State])

File is available here.

Please mark as solution if this works for you. Thumbs up for the effort is appreciated.

Kind regards, Steve.

Re: count of common values - only using DAX

Greatbi1 — Thu, 18 Jun 2020 06:56:42 GMT

Thank you steve for detailed explanantion. I really appreciate your approach.