topic Re: Get count of items that haven't been viewed in a selected timeframe in DAX Commands and Tips

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

SahityaYeruband — Mon, 09 Sep 2024 12:34:53 GMT

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.

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

Selva-Salimi — Mon, 09 Sep 2024 12:53:23 GMT

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.

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

SahityaYeruband — Mon, 09 Sep 2024 13:29:46 GMT

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.

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

Selva-Salimi — Mon, 09 Sep 2024 13:44:55 GMT

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.

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

SahityaYeruband — Mon, 09 Sep 2024 14:01:38 GMT

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.

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

Selva-Salimi — Mon, 09 Sep 2024 15:28:59 GMT

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.

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

Anonymous — Tue, 10 Sep 2024 06:01:26 GMT

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.

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

SahityaYeruband — Tue, 10 Sep 2024 07:27:38 GMT

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.

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

Selva-Salimi — Tue, 10 Sep 2024 10:47:25 GMT

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.

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

SahityaYeruband — Thu, 12 Sep 2024 09:11:21 GMT

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