Need a measure for comparing column values and returning a value based off repeated IDs

deannapi — Thu, 04 Apr 2024 22:26:59 GMT

I have an ID and a Source column. The ID repeats and the available Sources are "Web" and "Other".
User selects a value from a slicer on the page (from another column) that filters the IDs.

If an ID is repeated and it belongs to web and also other, then the final result for that ID needs to be "Other".

If the ID repeats and all rows belong to Web, then Web - but Other is higher than Web.

I've tried the following measure and it works, but when I add other columns to my table view, it times out

True Source = SWITCH( MINX( MyTable, SWITCH( MyTable[Origin], "Other", 1, "Web", 2, 3 ) ), 1, "Other", 2, "Web", "n/a" )

Then I tried this but EARLIER can only be used as a calculate column and the final result is wrong.

True Source v2 = VAR RepeatedIDCount = COUNTROWS( FILTER( MyTable, MyTable[ID] = EARLIER(MyTable[ID]) ) ) RETURN SWITCH ( TRUE (), RepeatedIDCount > 1 && COUNTROWS ( FILTER ( MyTable, MyTable[ID] = EARLIER(MyTable[ID]) && (MyTable[Origin] = "Web" || MyTable[Origin] = "Other") ) ) = 2, "Other", RepeatedIDCount = 1 && MyTable[Origin] = "Web", "Web", "n/a" )

A sample of the data would be:

ID	Source
8145378	Web
8145378	Web
8145377	Other
8145377	Other
8145382	Web
8145381	Other
8145381	Web
8145370	Other
8145370	Other

so the result table should be:

ID	True Source
8145378	Web
8145382	Other
8145377	Other
8145382	Web
8145381	Other
8145370	Other

Re: Need a measure for comparing column values and returning a value based off repeated IDs

lbendlin — Thu, 04 Apr 2024 22:17:02 GMT

why is 8145382 Web ?

Re: Need a measure for comparing column values and returning a value based off repeated IDs

deannapi — Thu, 04 Apr 2024 22:27:24 GMT

typo, I corrected my post

Re: Need a measure for comparing column values and returning a value based off repeated IDs

lbendlin — Thu, 04 Apr 2024 22:57:16 GMT

Measure = MIN(Table[Source])

Or implicit in the visual

Re: Need a measure for comparing column values and returning a value based off repeated IDs

johnbasha33 — Fri, 05 Apr 2024 09:27:46 GMT

deannapi have you tried implicit measures..

topic Need a measure for comparing column values and returning a value based off repeated IDs in DAX Commands and Tips

Need a measure for comparing column values and returning a value based off repeated IDs

Re: Need a measure for comparing column values and returning a value based off repeated IDs

Re: Need a measure for comparing column values and returning a value based off repeated IDs

Re: Need a measure for comparing column values and returning a value based off repeated IDs

Re: Need a measure for comparing column values and returning a value based off repeated IDs