topic Re: Lost Product in DAX Commands and Tips

Lost Product

H_insight — Wed, 10 Nov 2021 12:17:05 GMT

Hi All,

I am trying to identify lost product within my sample file for over 3 months ago. So All products thats meet the below criteria and over than 3 months old (rolling).

Criteria as follows: 1. product (col)= Production 2. Action (col) = View 3. Period is rolling last 3 months

Here is my DAX attempt:

Lost Product = VAR _Period = DATESINPERIOD(Data[Date],MAX(Data[Date]),-3,MONTH) Return CALCULATE(DISTINCTCOUNT(Data[Product]),NOT(Data[Date]) in _Period,FILTER(Data, Data[Product] ="Production"),FILTER(Data,Data[Action] = "View"))

The above dax return 1 record only, where I was expecting 4 records.

Any advice or tips would be much appreciated.

Sample file Link.

Thanks, H

Re: Lost Product

smpa01 — Wed, 10 Nov 2021 12:55:03 GMT

H_insight Did you need this?

Lost Product = VAR _Period = DATESINPERIOD ( Data[Date], MAX ( Data[Date] ), -3, MONTH ) RETURN CALCULATE ( COUNTROWS ( Data ), NOT ( Data[Date] ) IN _Period, FILTER ( Data, Data[Product] = "Production" && Data[Action] = "View" ) )

Re: Lost Product

H_insight — Wed, 10 Nov 2021 13:37:15 GMT

Hi smpa01

It works fine, but when I drag the date column nothing shows, any idea?

Thanks, H

Re: Lost Product

smpa01 — Wed, 10 Nov 2021 13:38:41 GMT

H_insight can you please provide your desired output clearly?

Re: Lost Product

H_insight — Wed, 10 Nov 2021 13:55:57 GMT

Expected outcome like this:

with the new measure the output shows the correct result (4 records) but when I add "Date" column, nothing shows in PBI.

Re: Lost Product

v-yanjiang-msft — Mon, 15 Nov 2021 01:37:00 GMT

Hi H_insight ,

According to your description, here’s my solution. “Lost Product” is a column. as in the visual, the same options are not displayed repeatedly, so they add up to 3.

Best Regards,
Community Support Team _ kalyj

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

Re: Lost Product

H_insight — Mon, 15 Nov 2021 10:53:45 GMT

Hi v-yanjiang-msft

How can you refrence columns in the IF functions as it shows as error? unless you want me to creat a calculated column?

Lost Product 3 = VAR _Period = DATESINPERIOD ( Data[Date], MAX ( Data[Date] ), -3, MONTH ) RETURN IF( 'Data'[Product] = "Production" && Data[Action] = "View" && NOT('Data'[Date]) IN _Period,1, IF( 'Data'[Product] = "Production" && Data[Action] = "View",0,BLANK()))

Re: Lost Product

v-yanjiang-msft — Tue, 30 Nov 2021 09:37:08 GMT

Hi H_insight ,

In my sample, Lost Product is a calculated column, I also create a measure in my sample, it cannot get the expected result, I attach it bellow to help you understand.

Best Regards,
Community Support Team _ kalyj

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

Re: Lost Product

H_insight — Tue, 30 Nov 2021 11:26:48 GMT

Hi v-yanjiang-msft ,

Thanks again for your attempt, much appreciated. I realised the measure won't get me the expected result. For now I will accept this as a soultion.

Thanks, H