Forum Discussion
thmonte
Helper IV
Helper IVCreating a value column for each time an item appears within a single date
I have a table that has the first 4 columns here. I want to try and create a new column that breaks the "items' down to everytime they appear within a date range regardless of "Store". so COUNT ...
Hi thmonte,
You could create a measure similar to:
Column3 = VAR myCount1 = IF ( Table3[Region] IN { "UK", "US" }, 0, ( CALCULATE ( COUNTROWS ( 'Table3' ), FILTER ( ALLEXCEPT ( 'Table3', Table3[Date] ), Table3[Item 1] = EARLIER ( 'Table3'[Item 1] ) && Table3[Region] <> "US" && Table3[Region] <> "UK" ) ) + CALCULATE ( COUNTROWS ( 'Table3' ), FILTER ( ALLEXCEPT ( 'Table3', Table3[Date] ), Table3[Item 2] = EARLIER ( 'Table3'[Item 1] ) && Table3[Region] <> "US" && Table3[Region] <> "UK" ) ) ) ) VAR myCount1percent = IF ( 'Table3'[Item 1] <> "null", DIVIDE ( 1 / myCount1, 1 ), 0 ) VAR myCount2 = IF ( Table3[Region] IN { "UK", "US" }, 0, ( CALCULATE ( COUNTROWS ( 'Table3' ), FILTER ( ALLEXCEPT ( 'Table3', Table3[Date] ), Table3[Item 1] = EARLIER ( 'Table3'[Item 2] ) && Table3[Region] <> "US" && Table3[Region] <> "UK" ) ) + CALCULATE ( COUNTROWS ( 'Table3' ), FILTER ( ALLEXCEPT ( 'Table3', Table3[Date] ), Table3[Item 2] = EARLIER ( 'Table3'[Item 2] ) && Table3[Region] <> "US" && Table3[Region] <> "UK" ) ) ) ) VAR myCount2percent = IF ( Table3[Item 2] <> "null", DIVIDE ( 1 / myCount2, 1 ), 0 ) RETURN myCount2percent + myCount1percentBest regards,
Yuliana Gu
thmonteHelper IV
Helper IVThis worked great! Thank you for the response. If I wanted to take this one step further and filter out regions that = US and UK and not have them get counted, would that be possible?
| Date | Region | Store | Item 1 | Item 2 | Value | Total |
| 11/12/2017 | US | Store A | Item A | Item B | 0.58 | |
| 11/12/2017 | US | Store A | Item A | Item B | 0.58 | |
| 11/12/2017 | UK | Store A | Item A | Item C | 0.75 | |
| 11/12/2017 | CA | Store B | Item A | null | 1 | 1 |
| 11/12/2017 | CA | Store C | Item B | Item C | 1 + 1 | 2 |
| 11/12/2017 | CA | Store C | Item D | null | 1 | 1 |
| Final Count | 4 |
v-yulgu-msft
Microsoft Employee
Microsoft EmployeeHi thmonte,
You could create a measure similar to:
Column3 =
VAR myCount1 =
IF (
Table3[Region] IN { "UK", "US" },
0,
(
CALCULATE (
COUNTROWS ( 'Table3' ),
FILTER (
ALLEXCEPT ( 'Table3', Table3[Date] ),
Table3[Item 1] = EARLIER ( 'Table3'[Item 1] )
&& Table3[Region] <> "US"
&& Table3[Region] <> "UK"
)
)
+ CALCULATE (
COUNTROWS ( 'Table3' ),
FILTER (
ALLEXCEPT ( 'Table3', Table3[Date] ),
Table3[Item 2] = EARLIER ( 'Table3'[Item 1] )
&& Table3[Region] <> "US"
&& Table3[Region] <> "UK"
)
)
)
)
VAR myCount1percent =
IF ( 'Table3'[Item 1] <> "null", DIVIDE ( 1 / myCount1, 1 ), 0 )
VAR myCount2 =
IF (
Table3[Region] IN { "UK", "US" },
0,
(
CALCULATE (
COUNTROWS ( 'Table3' ),
FILTER (
ALLEXCEPT ( 'Table3', Table3[Date] ),
Table3[Item 1] = EARLIER ( 'Table3'[Item 2] )
&& Table3[Region] <> "US"
&& Table3[Region] <> "UK"
)
)
+ CALCULATE (
COUNTROWS ( 'Table3' ),
FILTER (
ALLEXCEPT ( 'Table3', Table3[Date] ),
Table3[Item 2] = EARLIER ( 'Table3'[Item 2] )
&& Table3[Region] <> "US"
&& Table3[Region] <> "UK"
)
)
)
)
VAR myCount2percent =
IF ( Table3[Item 2] <> "null", DIVIDE ( 1 / myCount2, 1 ), 0 )
RETURN
myCount2percent + myCount1percent
Best regards,
Yuliana Gu