Forum Discussion
Helper IIIFind value at latest date & Time
Hi,
I have a table of permssions, with the changelog datetime entry (two integers with format like 20200305 (5th of march 2020)
and 6395 (at 00:06:95).
I want to find the latest value of PermissionType of each Customer (The user would be able to select a date , so has to be dynamically calculated). I then want to count how many of each PermissionType was active (e.g. the latest changelog event) at the selected date.
| PermissionID | CustomerID | PermissionDate | PermissionTime | PermissionType |
| 1 | 1 | 20200101 | 10736 | A |
| 2 | 1 | 20200224 | 875 | B |
| 3 | 2 | 20200305 | 4433 | B |
| 4 | 3 | 20200305 | 6395 | C |
So far I've gotten this:
6 Replies
You can refet to the following measure
Measure = var a=CALCULATE(MAX(Permissions[Permission Acquisition Date]),ALLEXCEPT(Permissions,Permissions[CustomerID])) var b=CALCULATE(MAX(Permissions[Permission Acquisition Time]),ALLEXCEPT(Permissions,Permissions[CustomerID]),Permissions[Permission Acquisition Date]=a) return CALCULATE(MAX(Permissions[PermissionType2]),ALLEXCEPT(Permissions,Permissions[CustomerID]),Permissions[Permission Acquisition Date]=a,Permissions[Permission Acquisition Time]=b)Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
AlexaderMillandNo idea what this is supposed to give, it's not even close to correct atleast 😕
According to my understanding, your need is to ask for the number of customers with the largest PermissionType, in the example, the largest PermissionType is c, and only the PermissionType of customers with CustomerID=3 is c, so the output is 1. If my understanding is wrong, please point it out and provide the output you want.
Best Regards!
Yolo Zhu
- AlexaderMilland
Maybe wasn't too clear. I don't need a table with each customerID to show. I need the total value of how many would have type C. E.g. how many times is C the latest active permission (based on the selected date).
The shown visual would by a card with "C" and it would count how many unique CustomerIDs would return C in your above graph.
Based on the measure i have offered, you can create a measure by countax() function.
e.g
Measure 2 = var a=SUMMARIZE(ALLSELECTED(Permissions),[CustomerID],"Max",[Measure]) return COUNTAX(FILTER(a,[Max]=MAXX(a,[Max])),[CustomerID])Best Regards!
Yolo Zhu
