Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Objective
For each distrinct value, find the latest date in the second column and the corresponding text value in the third column.
I have three columns: Guest, Host and Report Date. For each guest, I need to find the corresponding host name for the latest date. Yet, each guest has multiple hosts on different dates. Here is an example:
Guest | Host | Report Date |
Server A | Host A | 12/1/2016 |
Server A | Host A | 12/5/2016 |
Server A | Host B | 12/19/2016 |
The last row is the value I want to return. The Guest list is about 20,000 long so I want to return for each distrinct guest, a host name and the latest report date.
Thank you,
Tim
Solved! Go to Solution.
Hi @timstacy1,
You can use below measure to get the lastdate of each host.
maxDate=
var currGuest= lastNoBlan(Table[Guest],[Guest])
var currHost=lastNoBlan(Table[Host],[Host])
Return
MAXX(Filter(ALL(Table),[Guest]=currGuest&&[Host]=currHost),[Report Date])
Use [Guest], [Host] and above measure to create a table visual and you will get the result which you want.
Regards,
Xiaoxin Sheng
Hi @timstacy1,
You can use below measure to get the lastdate of each host.
maxDate=
var currGuest= lastNoBlan(Table[Guest],[Guest])
var currHost=lastNoBlan(Table[Host],[Host])
Return
MAXX(Filter(ALL(Table),[Guest]=currGuest&&[Host]=currHost),[Report Date])
Use [Guest], [Host] and above measure to create a table visual and you will get the result which you want.
Regards,
Xiaoxin Sheng
This is a clean solution. Thank you. The only change is to replace the typo to LASTNONBLANK.
Thank you. Here is the result I get:
"The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value." I copied your formula exactly. Could I have formatted the data incorrectly?
MaxReportDateHost(ByGuest)= VAR vRELEVANTDATE = [MaxReportDate(ByGuest)] VAR vCOUNTHOSTS = CALCULATE(DISTINCTCOUNT(Table[Host]), FILTER(Table, Table[Report Date] = vRELEVANTDATE)) RETURN IF( vCOUNTHOSTS > 1, "Multiple", CALCULATE( VALUES(Table[Host]), FILTER(Table, Table[Report Date] = vRELEVANTDATE ) )
Sorry I was a little sloppy with my DAX 🙂 This should be better
Thank you. When I first create the measure MaxReportDate(byguest), I receive an error message indicating"... 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."
If you are able, please confirm the measure is correct and I will try again.
Thank you
Great cool dude . lets rockzzzzzzzzz
Something to get you started ... You want to look at each distinct guest and figure out the latest date:
MaxReportDate(ByGuest)=MAXX( VALUES(Table[Guest]), Table[Report Date])
And then you also want to figure out who the host was on that date:
MaxReportDateHost(ByGuest)= VAR vRELEVANTDATE = [MaxReportDate(ByGuest)] VAR vCOUNTHOSTS = DISTINCOUNT(Table[Host]), FILTER(Table, Table[Report Date] = vRELEVANTDATE)
RETURN
IF( vMULTIPLEHOSTS > 1, "Multiple", CALCULATE( VALUES(Table[Host]), FILTER(Table, Table[Report Date] = vRELEVANTDATE )
)
I can't really test this and it probably needs some tweaking, but maybe this gets you started.
Create one calculated table , usin the below query,
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |