Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I need to create 2 measures.
Sample data below:
Date | Item ID |
02.10.2020 (today) | A |
02.10.2020 | B |
02.10.2020 | C |
01.10.2020 | B |
28.09.2020 | C |
27.09.2020 | C |
Solved! Go to Solution.
Hello @pawelj795 ,
Please try this:
Measure 1 =
var t1_=
CALCULATETABLE (
VALUES ( 'Table'[Item ID] ),
FILTER (
ALL ( 'Table'[Date] ),
'Table'[Date] < MAX('Table'[Date]) )
)
var t2_=
CALCULATETABLE (
VALUES ( 'Table'[Item ID] ),
FILTER (
ALL ( 'Table'[Date] ),
'Table'[Date] = MAX('Table'[Date]) )
)
return
COUNTROWS(INTERSECT(t1_,t2_))
Measure 2 = CALCULATE(COUNT('Table'[Date]),ALLEXCEPT('Table','Table'[Item ID]))
Hello @pawelj795 ,
Please try this:
Measure 1 =
var t1_=
CALCULATETABLE (
VALUES ( 'Table'[Item ID] ),
FILTER (
ALL ( 'Table'[Date] ),
'Table'[Date] < MAX('Table'[Date]) )
)
var t2_=
CALCULATETABLE (
VALUES ( 'Table'[Item ID] ),
FILTER (
ALL ( 'Table'[Date] ),
'Table'[Date] = MAX('Table'[Date]) )
)
return
COUNTROWS(INTERSECT(t1_,t2_))
Measure 2 = CALCULATE(COUNT('Table'[Date]),ALLEXCEPT('Table','Table'[Item ID]))
The information you have provided is not making the problem clear to me. Can you please explain with an example.
These can be two measures what I can get from a high level
calculate(distinctcount(Table[Item ID]), Table[Date] =today())
calculate(distinctcount(Table[Item ID]), Table[Date] < today())
Appreciate your Kudos.
Hi @amitchandak
Alluding to my above sample, I explain what I meant.
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |