Measure to compare two columns of different tables

[Anonymous]

Hi,

I am trying to create a measure in Power BI to compare two text columns of different tables. 

I have tried using the below dax but  this is giving me error because Item A and Item B are different tables. I am sure I am doing something wrong here.

TYPE =
VAR TEST =
COUNTROWS(
FILTER('Item B',
'Item B'[Item B Business Group] = 'Item A'[Item A Business Group]
&& 'Item B'[Item B Department] <> 'Item A'[Item A Department]
)
)
Return
IF(TEST = 1, "Same Group Different Dept", " Different Group Different Department")

Is there any other way I can do this. My datasource is a SSAS tabular model live connection to Power BI.

Here is my sample data : 

Item A Department Desc	Item A Business Group Desc	Item A Code	Item B Department Desc	Item B Business Group Desc	Item B Code
BABYCARE	TOYS	395	BABYWEAR	CHILDRENSWEAR	355
BABYCARE	TOYS	395	BATHROOM	HOMEWARES	590
BABYCARE	TOYS	395	BOOKS	LIFESTYLE ENTERTAINMENT	805
BABYCARE	TOYS	395	BOYS 1-7	CHILDRENSWEAR	350
BABYCARE	TOYS	395	BOYS TOYS	TOYS	851
BABYCARE	TOYS	395	CARDS AND WRAP	LIFESTYLE ENTERTAINMENT	845
BABYCARE	TOYS	395	CHILDRENS UNDERWEAR	CHILDRENSWEAR	370
BABYCARE	TOYS	395	D NURSERY	TOYS	905

Any help how I can get this working ?

[Stachu]

is there a join between the tables? If yes then you should use RELATED
https://docs.microsoft.com/en-us/dax/related-function-dax

if there i no join then, I'd suggest using TREATAS, similarly to described here:
https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/

[Anonymous]

Tried to use Related but still doesn't work


TYPE =
VAR TEST =
COUNTROWS(
FILTER('Item B',
RELATED('Item A'[Item A Business Group]) = ('Item B'[Item B Business Group Desc])
&& 'Item B'[Item B Department] <> RELATED('Item A'[Item A Department])
)
)
Return
IF(TEST = 1, "Same Group Different Dept", " Different Group Different Department")

also tried with TREATAS, I have got August 2017 Power BI Desktop and I don't think this function is supported yet on my power bi desktop.

Any help please?

[Stachu]

is there any relationship between the tables?

do you have any particular reason for using old version of PowerBI? can you upgrade? there have been lot of functions added in last 2 years, and they may be useful for your usecase

regarding your logic - 
filtering by ItemA, you want to count all the rows in ItemB that have the same Group, but different Department

I don't get your conditional though - should't it be
IF( TEST >=1...)?
Also 0 in this case may mean same group same department as well as different group different department, is that intentional?

[Anonymous]

We are using 2016 analysis server which my data source. And I think this DAX fucntion is still not availabe in analysis server.

I want the logic like

IF      Item B Business Group = Item A Business Group
                && Item B Department not equal to Item A Department

                    Then Same Group Different Dept
                           ELSE  Different Group Different Department

Item A and Item B are two tables that are joined to a fact table.

Any suggestions on how I can write a dax measure for this ?

[Anonymous]

Did you find the solution 😀 ?  Can you tell us more ? 

I have the same issue, I want to calculate the difference between 2 column for 2 separated matrix with differents filters on the same data, and obviously few people contribute 😞 and give actual answer to your question. Can anybody help ?