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 want to create a dax formula that will produce a list of unique guest values from Column A, and find only the distinct value in column B (Host) based on the latest date. The final report will only have a list of distinct guests and one host per guest. Although, I would expect to see multiple hosts from the entire list. The answer below should be as follows:
Server A has a Host A because the last report date is 04/17/2017.
Thank you,
Tim
Guest | Host | Report Date |
server A | Host A | 4/17/2017 |
server A | Host B | 4/10/2017 |
server A | Host C | 4/3/2017 |
server A | Host D | 3/20/2017 |
server A | Host E | 2/20/2017 |
server A | Host F | 2/13/2017 |
Solved! Go to Solution.
Hi @timstacy1,
It seems that there are some Guests have multiple Hosts with the same last Report Date. For example, Server A has both Host A and Host B with the last report date 04/17/2017 in your table like below.
Guest | Host | Report Date |
server A | Host A | 4/17/2017 |
server A | Host B | 4/17/2017 |
server A | Host C | 4/3/2017 |
server A | Host D | 3/20/2017 |
server A | Host E | 2/20/2017 |
server A | Host F | 2/13/2017 |
So what's your expected result in this scenario, show only Host A, or both Host A and B for Server A in the list?
If you still need a list of distinct guests and one host per guest, the formula below show work in your scenario.
HostLatestReport = CALCULATE ( FIRSTNONBLANK ( Table2[Host], 1 ), FILTER ( ALLEXCEPT ( Table2, Table2[Guest] ), Table2[Report Date] = [LatestReport] ) )
Regards
Hi @timstacy1,
Glad to help! Could you accept helpful replies as solution to close this thread?
Regards
Hi @timstacy1,
It seems that there are some Guests have multiple Hosts with the same last Report Date. For example, Server A has both Host A and Host B with the last report date 04/17/2017 in your table like below.
Guest | Host | Report Date |
server A | Host A | 4/17/2017 |
server A | Host B | 4/17/2017 |
server A | Host C | 4/3/2017 |
server A | Host D | 3/20/2017 |
server A | Host E | 2/20/2017 |
server A | Host F | 2/13/2017 |
So what's your expected result in this scenario, show only Host A, or both Host A and B for Server A in the list?
If you still need a list of distinct guests and one host per guest, the formula below show work in your scenario.
HostLatestReport = CALCULATE ( FIRSTNONBLANK ( Table2[Host], 1 ), FILTER ( ALLEXCEPT ( Table2, Table2[Guest] ), Table2[Report Date] = [LatestReport] ) )
Regards
Hello,
Thank you. The measures generated the following erros:
MdxScript(Model)(10,49) Calculation error in measure 'allguests'[hostlatestreport]: a table ofmultiple values was supplier where a single value was expected.
I have a long list of guest and host entries. Could this be the source of the error?
Thank you,
Tim
You are correct. I am only interested in a unique host for every unique guest, using the latest date as the determining factor. My apologies for the flawed table but glad you figured it out. I applied the measure and it worked! I can't believe it. I can't tell you what a help this is. Now, I need to figure out why it works. And, due to the size of the file I am working with, I run out of memory.
Thank you again!
Tim
Hi @timstacy1,
Glad to help! Could you accept helpful replies as solution to close this thread?
Regards
Hi Friend, Check this measure:
LatestReport = CALCULATE ( MAX ( Table2[Report Date] ), ALLEXCEPT ( Table2, Table2[Guest] ) )
HostLatestReport = CALCULATE ( VALUES ( Table2[Host] ), FILTER ( ALLEXCEPT ( Table2, Table2[Guest] ), Table2[Report Date] = [LatestReport] ) )
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 |
---|---|
113 | |
99 | |
76 | |
73 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |