Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to Solution.
why is 8145382 Web ?
typo, I corrected my post
Measure = MIN(Table[Source])
Or implicit in the visual