Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 ?
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/
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?
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?
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 ?
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 ?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
98 | |
80 | |
67 | |
63 |
User | Count |
---|---|
145 | |
111 | |
104 | |
84 | |
64 |