Same items sales

[M4R10]

I am looking for a way to build a filter that allows me to consider the sales for the same list of items through selected stores.
This means, consider the list of products that have sales greater than zero in all selected stores.

For example:

STORE A has sales for the folloing list of items:

item 1

item 2

item 3

item 4

and STORE B has sales for the following list of items:

item 3

item 4

item 5

The result that I'm looking for is:

TOTAL SAME ITEMS SALES for STORE A + STORE B should only consider:

item 3

item 4

thanks in advance for any help

regards,

Mario

[v-shex-msft]

Hi @M4R10,

>>The result that I'm looking for is: TOTAL SAME ITEMS SALES for STORE A + STORE B should only consider:

Current power bi slicer/filter not support this feature.
In my opinion, I'd like to suggest you write a measure to check with variable table(create by INTERSECT function) and return the tag.

Tag = 
VAR current_T1 =
    LASTNONBLANK ( T1[Items], [Items] )
VAR current_T2 =
    LASTNONBLANK ( T2[Items], [Items] )
VAR Selected =
    INTERSECT ( ALLSELECTED ( T1[Items] ), ALLSELECTED ( T2[Items] ) )
RETURN
    IF ( current_T1 IN Selected || current_T2 IN Selected, "Y", "N" )

Then drag this measure to visual level filter, switch to 'is' mode and filter on tag 'Y'.

Regards,

Xiaoxin Sheng

[M4R10]

thanks @v-shex-msft

I don't understand if your suggestion works for my model:

When you suggest to use two VARs related to T1[Items] and T2[Items], how should I take it considering I have only 1 items table?

What  Im trying to do is make a "live" inner join applied to items depending the store(s) selection

regards

[Ashish_Mathur]

Hi @M4R10,

Share the link from where i can download your file.

[M4R10]

Hi @Ashish_Mathur

I have uploaded the file to the following link:

https://we.tl/19AdsYIm1e

In this "Price" sampling I'm trying the apply the same rule regarding to identify "same items" across different days / stores.

thank you for any help.

[Ashish_Mathur]

Hi,

There is no file there.

[M4R10]

Hi @Ashish_Mathur

The link expired after a month.

I have already uploaded again, follows the link:

https://we.tl/52vnSlrFHH

Hope you can advise to me.

regards

[Ashish_Mathur]

Hi,

I do not understand the relatioships you have built.  Why is there no relatioship from the Date column of the tbl_price table to the date column of the tbl_calendar table?

[M4R10]

Hi @Ashish_Mathur

That is because the TBL_PRICES doesn't contain dates.

Dates are contained in the VISITS table, with a secondary PERIOD table to storage only the name of periods (PERIODS could be ommited anyway)

The model works as it is and all fields are connected through the current relationships.

Do you estimate the current structure and model could affect to reach a solution to the original question of the topic?