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 have the following audit table, which shows every time the [VendorID] [PaymentType] changes.
Though, what I want to track is the calculated column [Simplifed Acceptance], which is just a SWITCH statement that...simplifies, the [PaymentType] column a bit.
What I want to do in a Measure - say if a filter were set for 10/31/18 - it would show how many VendorID's last entry for that VendorID, on or prior to 10/31/18 were 'Check'. So hey, as of date <X>, the last entry for 1,000 VendorIDs was "check", so check = 1000.
The Measure would I guess, just be calculating the number of "Check" ones that are the most recent entry for tha VendorID, while the filter context would take care of the dates.
Right now I'm accomplishing all this by using SQL to create monthly snapshots of this table, then pull those tables into Power BI, have my SWITCH column, etc.
It works great, but it's a bit of a burden in terms of disk space. Example of that query to grab a snapshot.
select Vendorid ,sq.PaymentType from ( select VendorId ,PaymentType ,row_number() over (partition by vendorid order by createdateutc desc) as rn from ZpCustomers_Kim.dbo.VendorListPaymentTypeChangeAudit WHERE CreateDateUTC < '2018-11-01' ) sq where sq.rn = 1
I'd love to just import the Audit table itself though, and have a DAX forumla. I feel like EARLIER is a candiate for this (then again, maybe not)...but I'm haivng trouble thinking how to work it....
Solved! Go to Solution.
Hi @mmace1
A workaround, also the "calendar" table doesn't connect to your data table.
measure_createdate = MAX(Sheet1[CreateDate]) if_check = IF(MAX(Sheet1[Simplified Acceptance])="check",1,0) if_date = IF(MAX(Sheet1[CreateDate])<=[selected_max_date],1,0) flag = IF([if_date]=1&&[if_check]=1,1,0) rank_all = RANKX(FILTER(ALL(Sheet1),[flag]=1&&Sheet1[Vendorid]=MAX(Sheet1[Vendorid])),[measure_createdate],,DESC) rank_final = IF([flag]=1,[rank_all]) count = COUNTROWS(FILTER(ALL(Sheet1),[rank_final]=1))
Best Regards
Maggie
Hi @mmace1
A workaround, also the "calendar" table doesn't connect to your data table.
measure_createdate = MAX(Sheet1[CreateDate]) if_check = IF(MAX(Sheet1[Simplified Acceptance])="check",1,0) if_date = IF(MAX(Sheet1[CreateDate])<=[selected_max_date],1,0) flag = IF([if_date]=1&&[if_check]=1,1,0) rank_all = RANKX(FILTER(ALL(Sheet1),[flag]=1&&Sheet1[Vendorid]=MAX(Sheet1[Vendorid])),[measure_createdate],,DESC) rank_final = IF([flag]=1,[rank_all]) count = COUNTROWS(FILTER(ALL(Sheet1),[rank_final]=1))
Best Regards
Maggie
Hi @mmace1
If you create a Calendar Date table, add "date" in a slicer
Please don't connect the Calendar Date table with your audit table (don't create relationship between them),
Then create measures
selected_date_max = MAX('calendar'[Date]) Measure 2 = CALCULATE ( COUNT ( Sheet1[Vendorid] ), FILTER ( ALL ( Sheet1 ), Sheet1[CreateDate] <= [selected_date_max] && Sheet1[Simplified Acceptance] = "check" ) )
Best Regards
Maggie
Thanks - that would give the number of "Check" entries before a prior date, but the tricky part is only counting the *last entry* for that VendorID. If a given VendorID appeared as "Check" 10 times prior to 2018-10-31, then that measure would count all 10 times.
It's an Audit table, so say, a given VendorID could appear 100 times on it. I'm looking at what the "Simplified Acceptance" for every vendorID was on say, 2018-10-31: That would be the last "Simplified Acceptance" in the audit table, on or before 2018-10-31, which, for a given VendorID, would be the VendorID's "Simplified Acceptance" as of 2018-10-31.
I want the number of VendorIDs whose *last entry* on or prior to the selected date, was check.
Do I make sense? I kind of having trouble phrasing this.
Hi @mmace1
When you use "EARLIER", It need to be used in a column, also it use more memory than measure.
Best Regards
Maggie
Hi @mmace1
You could create a measure
Measure = CALCULATE ( COUNT ( Sheet1[Vendorid] ), FILTER ( ALL ( Sheet1 ), Sheet1[CreateDate] <= DATE ( 2018, 10, 31 ) && Sheet1[Simplified Acceptance] = "check" ) )
Best Regards
Maggie
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 |
---|---|
110 | |
99 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |