Get count of items that haven't been viewed in a selected timeframe

[SahityaYeruband]

Hi All,

I am trying to create a measure that counts the items that have not been viewed. This can be for the whole period (last 12 months) or for a specific month/quarter/year.
I have a dataset which contains details like - 
Item ID, user_id, viewed_timestamp, item_country, item_department etc .. 

Sample data : 

Item_ID	user_id	viewed_timestamp	Country	Dept
1	123	12/5/2023	USA	A
2			USA	A
3	234	2/1/2024	China	A
3	123	12/5/2023	China	A

Here, Item 2 was never viewed, item 1 was not viewed in Jan or Feb and Item 3 was not viewed in Jan.

So the outputs, when there are no time filters applied - 

Country	Dept	Items with 0 Viewes
USA	A	1
China	A	0

When December is selected : 

Country	Dept	Items with 0 Viewes
USA	A	2
China	A	0

When Jan is selcted :

Country	Dept	Items with 0 Viewes
USA	A	2
China	A	1

Similarly when Feb is selected :

Country	Dept	Items with 0 Viewes
USA	A	2
China	A	0

Also, I have a master list of Items along with it's details. The relationship between these 2 datasets is 1:M on the Item ID.

Thank you for your help.

[SahityaYeruband · Accepted solution]

The below worked - 
Very similar to the one suggested by @Selva-Salimi  - 
ViewedItems = CALCULATETABLE(COUNT(ViewedItems[Item_ID]),NOT(ISBLANK(ViewedItems[viewed_timestamp])))
All Items to be the same across different values of timestamp - 
AllItems = VALUES(MasterItems[Item_ID])
CALCULATE(COUNT(MasterItems[Item_ID]), ALL(ViewedItems[viewed_timestamp].[Year]),ALL(ViewedItems[viewed_timestamp].[Quarter]), ALL(ViewedItems[viewed_timestamp].[Month]))

UnViewed = AllItems - ViewedItems

[Selva-Salimi]

@SahityaYeruband 

It seems that you select the country from your fact table which is not correct. you should use a country "dimension" table instead. because the fact table will be filtered based on the selected slicers.

hope it helps!

If this post helps, then I would appreciate a thumbs up  and mark it as the solution to help the other members find it more quickly. 

[SahityaYeruband]

@Selva-Salimi I don't have a country dimension table, so I created one now. It had a 1:M relationship on country with the other 2 tables - Master List & Master Items.
I used the country field from the Dimension table in the visual instead of pulling it from the master item table.
It didn't work.

[Selva-Salimi]

hi @SahityaYeruband 

you can write a measure as follows:

UnviewedItemsCount =
VAR ViewedItems = CALCULATETABLE(VALUES(ViewedItems[Item_ID]),NOT(ISBLANK(ViewedItems[viewed_timestamp])))
VAR AllItems = VALUES(MasterItems[Item_ID])
VAR UnviewedItems = EXCEPT(AllItems, ViewedItems)
RETURN
COUNTROWS(UnviewedItems)

If this post helps, then I would appreciate a thumbs up  and mark it as the solution to help the other members find it more quickly. 

[SahityaYeruband]

Hi @Selva-Salimi ,
Thank you for the quick response. I have applied the logic as shared by you. However, its working only when no time filter is selected.

When selecting a specific time period (in the below case - yr = 2024)

Same for any other month in the available data as well.

[Selva-Salimi]

@SahityaYeruband 

did you want to ignore any filters on all items? then you might need to update that part as follows:

VAR AllItems = CALCULATETABLE( VALUES(MasterItems[Item_ID]), ALL(MasterItems) )

I did not understand exactly from the shared photos which part of the calculations it is related to? And why are there three tables?

If this post helps, then I would appreciate a thumbs up  and mark it as the solution to help the other members find it more quickly. 

[SahityaYeruband]

@Selva-Salimi 
Yeah ideally the complete marsters list needs to be considered. Sorry for not mentioning that before.
However, after making changes as suggested, there is no luck.
I have 3 visuals one for each measure created. I had 2 of these seperately so added the 3rd visual after creating the measure, sorry for any confusion there.

[Selva-Salimi]

@SahityaYeruband 

The relationship between these two tables should have a 'single' cross-filter direction from the dimension table to the fact table.

If this post helps, then I would appreciate a thumbs up  and mark it as the solution to help the other members find it more quickly. 

[Anonymous]

Hi @SahityaYeruband ,
Can you upload your pbix file, or a little more accurate example data, so that we can better understand your problem, and then help you to solve your difficulties, looking forward to your reply, after receiving your reply, I will be the first time to contact you!

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.