first per day
1 Topic
Get count of first interaction per day
Hello. A dax question for everyone. Two real tables my interactionFact (with all the messages) and a siteDim (with the list of sites). Essentially would like a count of the number of times each site interacts first. In whatever the given filter context is. What think should happen is A. Get list of dates (in current context), B. Get list of sites (in current context). C. Iterate each date, for each date get ALL interactions sent for that day. Clearing all the filters except the date D. Get first interaction for the day. E. Check if site in list of sites in filter F. Return 1 if so, if not What has me stuck is checking if the sitr is in the list. need to clear the filter to get the first interaction so that it's not the first interaction of that site for the day, but all sites of that day. I can't wrap my head around it though in DAX. I think there is two ways of going about it. Either getting the top row (via topn(1, interactionfact, interactionfact[timestamp], 1)), then returning a 1 if the site name is in the list of visible sites in the filter. Or getting the minimum time and calculating a table containing just those minimum times with a filter on the sites and getting the row count. Any thoughts? This the Dax have outlined. I'm stumped on the inner calculate. More of a python user, so this functional stuff is a bit of a thing to get use to. var _dates = VALUES(interactionFact[Date]) var _sites = VALUES(SiteDim[Name]) var result = SUMX( _dates, CALCULATE( ??? ) ) ..Solved1.2KViews0likes2Comments