Creating a value column for each time an item appears within a single date

[thmonte]

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 how many times a distinct item appears in "Item 1" OR Item 2" within a single day and divide it by 1 to give it a value.  Then once each distinct item has a value add the two for that specific row to come up with the "Total" column.

Date	Store	Item 1	Item 2	Value	Total
11/12/2017	Store A	Item A	Item B	.25 + .33	0.58
11/12/2017	Store A	Item A	Item B	.25 + .33	0.58
11/12/2017	Store A	Item A	Item C	.25 + .50	0.75
11/12/2017	Store B	Item A	null	.25	0.25
11/12/2017	Store C	Item B	Item C	.33 + .50	0.83
11/12/2017	Store C	Item D	null	1	1
					3.99

I wasn't really sure what to research on this as I feel its a pretty unique case.  Any help would be appreciated.

[v-yulgu-msft]

Hi @thmonte,

I made a little modification to Phil_Seamark's solution to take the date into account. Please try:

Column = 

VAR myCount1 = CALCULATE(COUNTROWS('Table3'),FILTER(ALLEXCEPT('Table3',Table3[Date]),Table3[Item 1] = EARLIER('Table3'[Item 1])))
             + CALCULATE(COUNTROWS('Table3'),FILTER(ALLEXCEPT('Table3',Table3[Date]),Table3[Item 2] = EARLIER('Table3'[Item 1])))
             
VAR myCount1percent = IF ('Table3'[Item 1] <> blank(), DIVIDE( 1 /   myCount1,1 ),0)

VAR myCount2 = CALCULATE(COUNTROWS('Table3'),FILTER(ALLEXCEPT('Table3',Table3[Date]),Table3[Item 1] = EARLIER('Table3'[Item 2])))
             + CALCULATE(COUNTROWS('Table3'),FILTER(ALLEXCEPT('Table3',Table3[Date]),Table3[Item 2] = EARLIER('Table3'[Item 2]))) 

VAR myCount2percent = IF(Table3[Item 2] <> BLANK(), DIVIDE( 1 / myCount2 , 1),0)

RETURN myCount2percent + myCount1percent

Replace ALL with ALLEXCEPT when calculating count values for each item.

Regards,
Yuliana Gu

[thmonte]

This 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 · Accepted solution]

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 + myCount1percent

Best regards,

Yuliana Gu

[thmonte]

This looks really close but almost all my numbers are off by .5.

In the simplest scenario item 1 is unique once and item 2 is unique once the expression should return 2.0.  In your example it is returning 1.5

Edit: disregard, i believe there was just a syntax error

[Phil_Seamark]

HI @thmonte

This is close and works on your sample data.  It probably needs some tweaking around the use of the ALL in the FITLER functions but that should be easy enough.

Column = 

VAR myCount1 = CALCULATE(COUNTROWS('Table3'),FILTER(ALL('Table3'),Table3[Item 1] = EARLIER('Table3'[Item 1])))
             + CALCULATE(COUNTROWS('Table3'),FILTER(ALL('Table3'),Table3[Item 2] = EARLIER('Table3'[Item 1])))
             
VAR myCount1percent = IF ('Table3'[Item 1] <> blank(), DIVIDE( 1 /   myCount1,1 ),0)

VAR myCount2 = CALCULATE(COUNTROWS('Table3'),FILTER(ALL('Table3'),Table3[Item 1] = EARLIER('Table3'[Item 2])))
             + CALCULATE(COUNTROWS('Table3'),FILTER(ALL('Table3'),Table3[Item 2] = EARLIER('Table3'[Item 2]))) 

VAR myCount2percent = IF(Table3[Item 2] <> BLANK(), DIVIDE( 1 / myCount2 , 1),0)

RETURN myCount2percent + myCount1percent