The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
Hi! I need some help figuring out how to count each of the values (letters in this case) without counting the values that occurred the day before. Please see the screenshot to explain a little better 🙂
Solved! Go to Solution.
@Syk Try:
Measure =
VAR __Date = MAX('Table'[Date])
VAR __DayBefore = __Date - 1
VAR __Values = DISTINCT('Table'[Value])
VAR __ValuesDayBefore = DISTINCT(SELECTCOLUMNS(FILTER(ALL('Table'),[Date]=__DayBefore),"Value",[Value])
RETURN
COUNTROWS(EXCEPT(__Values,__ValuesDayBefore))
Hi,
Here is one way to do this:
(my sample data is same as in your example)
The idea is to get two tables within a variable (reduce) and then compare them for matching values. The tables are constructed from current date (cdate) and lastdate (ldate). If this interect table's row count is more than 0 it is reduced from the count.
I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!
My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/
Proud to be a Super User!
Hi @Syk
Try this code
Count =
VAR CurrentTable =
SELECTCOLUMNS ( Table, "@Value", Table[Value] )
VAR DayBeforeTable =
CALCULATETABLE ( CurrentTable, Table[Date] = Table[Date] - 1 )
VAR ExceptTable =
EXCEPT ( DayBeforeTable, CurrentTable )
VAR Result =
COUNTROWS ( ExceptTable )
RETURN
Result
@Syk Try:
Measure =
VAR __Date = MAX('Table'[Date])
VAR __DayBefore = __Date - 1
VAR __Values = DISTINCT('Table'[Value])
VAR __ValuesDayBefore = DISTINCT(SELECTCOLUMNS(FILTER(ALL('Table'),[Date]=__DayBefore),"Value",[Value])
RETURN
COUNTROWS(EXCEPT(__Values,__ValuesDayBefore))
User | Count |
---|---|
28 | |
11 | |
8 | |
6 | |
5 |
User | Count |
---|---|
35 | |
14 | |
12 | |
9 | |
7 |