DAX Index and Match?
Good Afternoon,
What I am looking to do in Power Pivot is to bring a value dependant on two other columns within the same table.
What I did first: got the latest date for a name entered in the table. I now need to match an ID number based on teh name and the date.
For example:
ID | Name | Date | Latest Date | ID Number
1 | John | 1/1/2018 | 1/5/2018 |
2 | Kyle | 1/15/2018 | 1/30/2018 |
3 | John | 1/5/2018 | 1/5/2018 |
4 | Kyle | 1/30/2018 | 1/30/2018 |
What I need is:
ID | Name | Date | Latest Date | ID Number
1 | John | 1/1/2018 | 1/5/2018 | 3
2 | Kyle | 1/15/2018 | 1/30/2018 | 4
3 | John | 1/5/2018 | 1/5/2018 | 3
4 | Kyle | 1/30/2018 | 1/30/2018 | 4
Any ideas on how to accomplish script to accoplish this?
function dax powerpivot
edited Nov 13 '18 at 21:07
Alexis Olson
13.7k21734
asked Nov 13 '18 at 20:42
KLombKLomb
112
add a comment |
Good Afternoon,
What I am looking to do in Power Pivot is to bring a value dependant on two other columns within the same table.
What I did first: got the latest date for a name entered in the table. I now need to match an ID number based on teh name and the date.
For example:
ID | Name | Date | Latest Date | ID Number
1 | John | 1/1/2018 | 1/5/2018 |
2 | Kyle | 1/15/2018 | 1/30/2018 |
3 | John | 1/5/2018 | 1/5/2018 |
4 | Kyle | 1/30/2018 | 1/30/2018 |
What I need is:
ID | Name | Date | Latest Date | ID Number
1 | John | 1/1/2018 | 1/5/2018 | 3
2 | Kyle | 1/15/2018 | 1/30/2018 | 4
3 | John | 1/5/2018 | 1/5/2018 | 3
4 | Kyle | 1/30/2018 | 1/30/2018 | 4
Any ideas on how to accomplish script to accoplish this?
function dax powerpivot
edited Nov 13 '18 at 21:07
Alexis Olson
13.7k21734
asked Nov 13 '18 at 20:42
KLombKLomb
112
add a comment |
Good Afternoon,
What I am looking to do in Power Pivot is to bring a value dependant on two other columns within the same table.
What I did first: got the latest date for a name entered in the table. I now need to match an ID number based on teh name and the date.
For example:
ID | Name | Date | Latest Date | ID Number
1 | John | 1/1/2018 | 1/5/2018 |
2 | Kyle | 1/15/2018 | 1/30/2018 |
3 | John | 1/5/2018 | 1/5/2018 |
4 | Kyle | 1/30/2018 | 1/30/2018 |
What I need is:
ID | Name | Date | Latest Date | ID Number
1 | John | 1/1/2018 | 1/5/2018 | 3
2 | Kyle | 1/15/2018 | 1/30/2018 | 4
3 | John | 1/5/2018 | 1/5/2018 | 3
4 | Kyle | 1/30/2018 | 1/30/2018 | 4
Any ideas on how to accomplish script to accoplish this?
function dax powerpivot
edited Nov 13 '18 at 21:07
Alexis Olson
13.7k21734
asked Nov 13 '18 at 20:42
KLombKLomb
112
Good Afternoon,
What I am looking to do in Power Pivot is to bring a value dependant on two other columns within the same table.
What I did first: got the latest date for a name entered in the table. I now need to match an ID number based on teh name and the date.
For example:
ID | Name | Date | Latest Date | ID Number
1 | John | 1/1/2018 | 1/5/2018 |
2 | Kyle | 1/15/2018 | 1/30/2018 |
3 | John | 1/5/2018 | 1/5/2018 |
4 | Kyle | 1/30/2018 | 1/30/2018 |
What I need is:
ID | Name | Date | Latest Date | ID Number
1 | John | 1/1/2018 | 1/5/2018 | 3
2 | Kyle | 1/15/2018 | 1/30/2018 | 4
3 | John | 1/5/2018 | 1/5/2018 | 3
4 | Kyle | 1/30/2018 | 1/30/2018 | 4
Any ideas on how to accomplish script to accoplish this?
function dax powerpivot
function dax powerpivot
edited Nov 13 '18 at 21:07
Alexis Olson
13.7k21734
asked Nov 13 '18 at 20:42
KLombKLomb
112
edited Nov 13 '18 at 21:07
Alexis Olson
13.7k21734
asked Nov 13 '18 at 20:42
KLombKLomb
112
edited Nov 13 '18 at 21:07
Alexis Olson
13.7k21734
edited Nov 13 '18 at 21:07
Alexis Olson
13.7k21734
edited Nov 13 '18 at 21:07
Alexis Olson
13.7k21734
13.7k21734
asked Nov 13 '18 at 20:42
KLombKLomb
112
asked Nov 13 '18 at 20:42
KLombKLomb
112
asked Nov 13 '18 at 20:42
KLombKLomb
112
112
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
The closest equivalent is the LOOKUPVALUE function.
This should work as a calculated column:
= LOOKUPVALUE(Table1[ID], Table1[Date], [Latest Date])
There are other ways to do this using filtering, but the above should be the most familiar.
answered Nov 13 '18 at 21:11
Alexis OlsonAlexis Olson
13.7k21734
Thanks, however I keep getting an error. I think I forgot to mention that it is the ID based on the Name and the Latest Date.
– KLomb
Nov 13 '18 at 22:56
This will break if there are multiple matchingIDvalues for the values you are matching on. That is, if there isn't a single uniqueIDgiven a specific name and date. Can you double check that your matching always returns a single value?
– Alexis Olson
Nov 14 '18 at 14:54
add a comment |
You can use LOOKUPVALUE if you will only ever have one ID number per Latest Date / Name.
Using this to return the ID which matches the Name and the Latest Date:
As a calculated column;
LOOKUPVALUE (
Table1[ID],
Table1[Name], [Name],
Table1[Date], [Latest Date]
)
As a measure;
Latest ID:=
VAR LookupName =
IF (
HASONEVALUE ( Table1[Name] ),
VALUES ( Table1[Name] ),
BLANK ()
)
VAR LookupDate =
IF (
HASONEVALUE ( Table1[Latest Date] ),
VALUES ( Table1[Latest Date] ),
BLANK ()
)
RETURN
LOOKUPVALUE (
Table1[ID],
Table1[Name], LookupName,
Table1[Date], LookupDate
)
However, if you have more than one ID number with a matching name and date which matches the Latest Date, then this will return an error:
A table of multiple values was supplied where a single value was expected
Instead, you could use:
Latest ID:=
VAR LookupDate =
IF (
HASONEVALUE ( Table1[Latest Date] ),
VALUES ( Table1[Latest Date] ),
BLANK ()
)
RETURN
CALCULATE (
FIRSTNONBLANK ( Table1[ID] , 1),
FILTER (
ALLEXCEPT ( Table1, Table1[Name] ),
Table1[Date] = LookupDate
)
)
answered Nov 14 '18 at 9:38
OllyOlly
3,8861927
Thanks, but none of these solutions are working. It's in PowerPivot and all in the same table. I used the following DAX function (at the bottom). Perhaps my syntax is incorrect? I don't know how, though. I've watched countless youtube videos and I continue to get a #ERROR. Could it be that the latest date was entered based on another DAX function and the system cannot return the value?
– KLomb
Nov 14 '18 at 14:49
=LOOKUPVALUE(table1[ID],table1[Name],[name],table1[date],[latestdate])
– KLomb
Nov 14 '18 at 14:49
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53289197%2fdax-index-and-match%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
The closest equivalent is the LOOKUPVALUE function.
This should work as a calculated column:
= LOOKUPVALUE(Table1[ID], Table1[Date], [Latest Date])
There are other ways to do this using filtering, but the above should be the most familiar.
answered Nov 13 '18 at 21:11
Alexis OlsonAlexis Olson
13.7k21734
Thanks, however I keep getting an error. I think I forgot to mention that it is the ID based on the Name and the Latest Date.
– KLomb
Nov 13 '18 at 22:56
This will break if there are multiple matchingIDvalues for the values you are matching on. That is, if there isn't a single uniqueIDgiven a specific name and date. Can you double check that your matching always returns a single value?
– Alexis Olson
Nov 14 '18 at 14:54
add a comment |
The closest equivalent is the LOOKUPVALUE function.
This should work as a calculated column:
= LOOKUPVALUE(Table1[ID], Table1[Date], [Latest Date])
There are other ways to do this using filtering, but the above should be the most familiar.
answered Nov 13 '18 at 21:11
Alexis OlsonAlexis Olson
13.7k21734
Thanks, however I keep getting an error. I think I forgot to mention that it is the ID based on the Name and the Latest Date.
– KLomb
Nov 13 '18 at 22:56
This will break if there are multiple matchingIDvalues for the values you are matching on. That is, if there isn't a single uniqueIDgiven a specific name and date. Can you double check that your matching always returns a single value?
– Alexis Olson
Nov 14 '18 at 14:54
add a comment |
The closest equivalent is the LOOKUPVALUE function.
This should work as a calculated column:
= LOOKUPVALUE(Table1[ID], Table1[Date], [Latest Date])
There are other ways to do this using filtering, but the above should be the most familiar.
answered Nov 13 '18 at 21:11
Alexis OlsonAlexis Olson
13.7k21734
The closest equivalent is the LOOKUPVALUE function.
This should work as a calculated column:
= LOOKUPVALUE(Table1[ID], Table1[Date], [Latest Date])
There are other ways to do this using filtering, but the above should be the most familiar.
answered Nov 13 '18 at 21:11
Alexis OlsonAlexis Olson
13.7k21734
answered Nov 13 '18 at 21:11
Alexis OlsonAlexis Olson
13.7k21734
answered Nov 13 '18 at 21:11
Alexis OlsonAlexis Olson
13.7k21734
answered Nov 13 '18 at 21:11
Alexis OlsonAlexis Olson
13.7k21734
13.7k21734
Thanks, however I keep getting an error. I think I forgot to mention that it is the ID based on the Name and the Latest Date.
– KLomb
Nov 13 '18 at 22:56
This will break if there are multiple matchingIDvalues for the values you are matching on. That is, if there isn't a single uniqueIDgiven a specific name and date. Can you double check that your matching always returns a single value?
– Alexis Olson
Nov 14 '18 at 14:54
add a comment |
Thanks, however I keep getting an error. I think I forgot to mention that it is the ID based on the Name and the Latest Date.
– KLomb
Nov 13 '18 at 22:56
This will break if there are multiple matchingIDvalues for the values you are matching on. That is, if there isn't a single uniqueIDgiven a specific name and date. Can you double check that your matching always returns a single value?
– Alexis Olson
Nov 14 '18 at 14:54
Thanks, however I keep getting an error. I think I forgot to mention that it is the ID based on the Name and the Latest Date.
– KLomb
Nov 13 '18 at 22:56
Thanks, however I keep getting an error. I think I forgot to mention that it is the ID based on the Name and the Latest Date.
– KLomb
Nov 13 '18 at 22:56
This will break if there are multiple matching
ID values for the values you are matching on. That is, if there isn't a single unique ID given a specific name and date. Can you double check that your matching always returns a single value?– Alexis Olson
Nov 14 '18 at 14:54
This will break if there are multiple matching
ID values for the values you are matching on. That is, if there isn't a single unique ID given a specific name and date. Can you double check that your matching always returns a single value?– Alexis Olson
Nov 14 '18 at 14:54
add a comment |
You can use LOOKUPVALUE if you will only ever have one ID number per Latest Date / Name.
Using this to return the ID which matches the Name and the Latest Date:
As a calculated column;
LOOKUPVALUE (
Table1[ID],
Table1[Name], [Name],
Table1[Date], [Latest Date]
)
As a measure;
Latest ID:=
VAR LookupName =
IF (
HASONEVALUE ( Table1[Name] ),
VALUES ( Table1[Name] ),
BLANK ()
)
VAR LookupDate =
IF (
HASONEVALUE ( Table1[Latest Date] ),
VALUES ( Table1[Latest Date] ),
BLANK ()
)
RETURN
LOOKUPVALUE (
Table1[ID],
Table1[Name], LookupName,
Table1[Date], LookupDate
)
However, if you have more than one ID number with a matching name and date which matches the Latest Date, then this will return an error:
A table of multiple values was supplied where a single value was expected
Instead, you could use:
Latest ID:=
VAR LookupDate =
IF (
HASONEVALUE ( Table1[Latest Date] ),
VALUES ( Table1[Latest Date] ),
BLANK ()
)
RETURN
CALCULATE (
FIRSTNONBLANK ( Table1[ID] , 1),
FILTER (
ALLEXCEPT ( Table1, Table1[Name] ),
Table1[Date] = LookupDate
)
)
answered Nov 14 '18 at 9:38
OllyOlly
3,8861927
Thanks, but none of these solutions are working. It's in PowerPivot and all in the same table. I used the following DAX function (at the bottom). Perhaps my syntax is incorrect? I don't know how, though. I've watched countless youtube videos and I continue to get a #ERROR. Could it be that the latest date was entered based on another DAX function and the system cannot return the value?
– KLomb
Nov 14 '18 at 14:49
=LOOKUPVALUE(table1[ID],table1[Name],[name],table1[date],[latestdate])
– KLomb
Nov 14 '18 at 14:49
add a comment |
You can use LOOKUPVALUE if you will only ever have one ID number per Latest Date / Name.
Using this to return the ID which matches the Name and the Latest Date:
As a calculated column;
LOOKUPVALUE (
Table1[ID],
Table1[Name], [Name],
Table1[Date], [Latest Date]
)
As a measure;
Latest ID:=
VAR LookupName =
IF (
HASONEVALUE ( Table1[Name] ),
VALUES ( Table1[Name] ),
BLANK ()
)
VAR LookupDate =
IF (
HASONEVALUE ( Table1[Latest Date] ),
VALUES ( Table1[Latest Date] ),
BLANK ()
)
RETURN
LOOKUPVALUE (
Table1[ID],
Table1[Name], LookupName,
Table1[Date], LookupDate
)
However, if you have more than one ID number with a matching name and date which matches the Latest Date, then this will return an error:
A table of multiple values was supplied where a single value was expected
Instead, you could use:
Latest ID:=
VAR LookupDate =
IF (
HASONEVALUE ( Table1[Latest Date] ),
VALUES ( Table1[Latest Date] ),
BLANK ()
)
RETURN
CALCULATE (
FIRSTNONBLANK ( Table1[ID] , 1),
FILTER (
ALLEXCEPT ( Table1, Table1[Name] ),
Table1[Date] = LookupDate
)
)
answered Nov 14 '18 at 9:38
OllyOlly
3,8861927
Thanks, but none of these solutions are working. It's in PowerPivot and all in the same table. I used the following DAX function (at the bottom). Perhaps my syntax is incorrect? I don't know how, though. I've watched countless youtube videos and I continue to get a #ERROR. Could it be that the latest date was entered based on another DAX function and the system cannot return the value?
– KLomb
Nov 14 '18 at 14:49
=LOOKUPVALUE(table1[ID],table1[Name],[name],table1[date],[latestdate])
– KLomb
Nov 14 '18 at 14:49
add a comment |
You can use LOOKUPVALUE if you will only ever have one ID number per Latest Date / Name.
Using this to return the ID which matches the Name and the Latest Date:
As a calculated column;
LOOKUPVALUE (
Table1[ID],
Table1[Name], [Name],
Table1[Date], [Latest Date]
)
As a measure;
Latest ID:=
VAR LookupName =
IF (
HASONEVALUE ( Table1[Name] ),
VALUES ( Table1[Name] ),
BLANK ()
)
VAR LookupDate =
IF (
HASONEVALUE ( Table1[Latest Date] ),
VALUES ( Table1[Latest Date] ),
BLANK ()
)
RETURN
LOOKUPVALUE (
Table1[ID],
Table1[Name], LookupName,
Table1[Date], LookupDate
)
However, if you have more than one ID number with a matching name and date which matches the Latest Date, then this will return an error:
A table of multiple values was supplied where a single value was expected
Instead, you could use:
Latest ID:=
VAR LookupDate =
IF (
HASONEVALUE ( Table1[Latest Date] ),
VALUES ( Table1[Latest Date] ),
BLANK ()
)
RETURN
CALCULATE (
FIRSTNONBLANK ( Table1[ID] , 1),
FILTER (
ALLEXCEPT ( Table1, Table1[Name] ),
Table1[Date] = LookupDate
)
)
answered Nov 14 '18 at 9:38
OllyOlly
3,8861927
You can use LOOKUPVALUE if you will only ever have one ID number per Latest Date / Name.
Using this to return the ID which matches the Name and the Latest Date:
As a calculated column;
LOOKUPVALUE (
Table1[ID],
Table1[Name], [Name],
Table1[Date], [Latest Date]
)
As a measure;
Latest ID:=
VAR LookupName =
IF (
HASONEVALUE ( Table1[Name] ),
VALUES ( Table1[Name] ),
BLANK ()
)
VAR LookupDate =
IF (
HASONEVALUE ( Table1[Latest Date] ),
VALUES ( Table1[Latest Date] ),
BLANK ()
)
RETURN
LOOKUPVALUE (
Table1[ID],
Table1[Name], LookupName,
Table1[Date], LookupDate
)
However, if you have more than one ID number with a matching name and date which matches the Latest Date, then this will return an error:
A table of multiple values was supplied where a single value was expected
Instead, you could use:
Latest ID:=
VAR LookupDate =
IF (
HASONEVALUE ( Table1[Latest Date] ),
VALUES ( Table1[Latest Date] ),
BLANK ()
)
RETURN
CALCULATE (
FIRSTNONBLANK ( Table1[ID] , 1),
FILTER (
ALLEXCEPT ( Table1, Table1[Name] ),
Table1[Date] = LookupDate
)
)
answered Nov 14 '18 at 9:38
OllyOlly
3,8861927
answered Nov 14 '18 at 9:38
OllyOlly
3,8861927
answered Nov 14 '18 at 9:38
OllyOlly
3,8861927
answered Nov 14 '18 at 9:38
OllyOlly
3,8861927
3,8861927
Thanks, but none of these solutions are working. It's in PowerPivot and all in the same table. I used the following DAX function (at the bottom). Perhaps my syntax is incorrect? I don't know how, though. I've watched countless youtube videos and I continue to get a #ERROR. Could it be that the latest date was entered based on another DAX function and the system cannot return the value?
– KLomb
Nov 14 '18 at 14:49
=LOOKUPVALUE(table1[ID],table1[Name],[name],table1[date],[latestdate])
– KLomb
Nov 14 '18 at 14:49
add a comment |
Thanks, but none of these solutions are working. It's in PowerPivot and all in the same table. I used the following DAX function (at the bottom). Perhaps my syntax is incorrect? I don't know how, though. I've watched countless youtube videos and I continue to get a #ERROR. Could it be that the latest date was entered based on another DAX function and the system cannot return the value?
– KLomb
Nov 14 '18 at 14:49
=LOOKUPVALUE(table1[ID],table1[Name],[name],table1[date],[latestdate])
– KLomb
Nov 14 '18 at 14:49
Thanks, but none of these solutions are working. It's in PowerPivot and all in the same table. I used the following DAX function (at the bottom). Perhaps my syntax is incorrect? I don't know how, though. I've watched countless youtube videos and I continue to get a #ERROR. Could it be that the latest date was entered based on another DAX function and the system cannot return the value?
– KLomb
Nov 14 '18 at 14:49
Thanks, but none of these solutions are working. It's in PowerPivot and all in the same table. I used the following DAX function (at the bottom). Perhaps my syntax is incorrect? I don't know how, though. I've watched countless youtube videos and I continue to get a #ERROR. Could it be that the latest date was entered based on another DAX function and the system cannot return the value?
– KLomb
Nov 14 '18 at 14:49
=LOOKUPVALUE(table1[ID],table1[Name],[name],table1[date],[latestdate])
– KLomb
Nov 14 '18 at 14:49
=LOOKUPVALUE(table1[ID],table1[Name],[name],table1[date],[latestdate])
– KLomb
Nov 14 '18 at 14:49
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53289197%2fdax-index-and-match%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
