Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
We have 2 different systems used to track inventory locations and want to show in PBI if they are varient or not. The two tables are related via a PRODUCT_KEY so it seem's like it should be a simple no brainer to just compare the location values with a measure:
MyMeasure = If(SystemATable[Location]=SystemBTable[Location],"Yes","No")
PBi returns:
A single value for column 'LOCATION' in table 'SystemATable' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.
I've googled the heck out of this and have tried using RELATED, RELATEDTABLE, EXACT, SWITCH, setting up the compared values as VARiables, etc ad nausium and either I get the tiresome aggrigate error
It would be a lot of work to merge these tables together to create a calculated column so I'd like to do with with a dax measure, is it possible? Seems like it would be easy peasy but nooooooooo 😞
Solved! Go to Solution.
@tamerj1 Got me closer!
Measure = IF(Values(TableA[Column]) = Values(TableB[Column]), "yes", "no"))
worked, VALUES being the text aggregate. Thank you!
@tamerj1 Got me closer!
Measure = IF(Values(TableA[Column]) = Values(TableB[Column]), "yes", "no"))
worked, VALUES being the text aggregate. Thank you!
it depends on your requirement
you may try
MyMeasure =
IF (
SystemATable[Location] IN RELATEDTABLE ( VALUES ( SystemBTable[Location] ) ),
"Yes",
"No"
)
Or
MyMeasure =
IF (
SystemATable[Location] IN VALUES ( SystemBTable[Location] ),
"Yes",
"No"
)
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
25 | |
13 | |
11 | |
9 | |
6 |