topic Re: Problems with COUNT in DAX Commands and Tips

Problems with COUNT

elandry9840 — Mon, 02 May 2022 18:57:09 GMT

Hi,

First post here. This should be simple for an experienced DAX person, and I'd greatly appreciate a suggestion or two. I have a table with weekly stock closing prices for a few thousand tickers. I want to calc the 40-week moving average for each ticker on each week, then count the number of tickers trading below their moving average for each week. Simple, but I can't get the last part...the count.

Here is the MA calc, which works perfectly:

40-week MA =

AVERAGEX(

DATESBETWEEN('Calendar'[Date], max('Calendar'[Date]) - 200, max('Calendar'[Date])),

Calculate(sum(PriceTable[closeadj]))

)

To count the number trading below the MA I'm using:

Number of Stocks < 200Day MA =

COUNTROWS(

filter('PriceTable',

PriceTable[closeadj] < [40-week MA]

)

This returns nothing, unfortunately.

The model is simple, with only 1 fact table (weekly pricetable) and two DIM tables (Calendar and Tickers). Can somebody please suggest what I'm doing wrong?

Thanks

Re: Problems with COUNT

tamerj1 — Mon, 02 May 2022 19:08:14 GMT

Hi elandry9840

how does your report look like? Can you share a screenshot of your data model?

Re: Problems with COUNT

elandry9840 — Mon, 02 May 2022 19:17:00 GMT

Sure thing! Thanks for the reply!!

Not sure if this will be big enough. If not, let me know.

Re: Problems with COUNT

elandry9840 — Mon, 02 May 2022 19:18:46 GMT

This is better:

Re: Problems with COUNT

tamerj1 — Mon, 02 May 2022 19:19:21 GMT

elandry9840

The screenshot is very small. However please try

Number of Stocks < 200Day MA =
VAR FortyWeekMA = [40-week MA]
RETURN
COUNTROWS ( FILTER ( 'PriceTable', PriceTable[closeadj] < FortyWeekMA ) )

Re: Problems with COUNT

elandry9840 — Mon, 02 May 2022 19:21:48 GMT

Best:

Re: Problems with COUNT

tamerj1 — Mon, 02 May 2022 19:32:18 GMT

elandry9840

Have you tried above code?

Re: Problems with COUNT

elandry9840 — Mon, 02 May 2022 19:53:24 GMT

Yes. Thank you, but doesn't seem to work. Must be something in my model that is peculiar.

Re: Problems with COUNT

tamerj1 — Mon, 02 May 2022 20:02:43 GMT

elandry9840

Please use the variable name inside FILTER same as the code I have shared

Re: Problems with COUNT

elandry9840 — Mon, 02 May 2022 20:23:48 GMT

Sorry. Here it is:

Re: Problems with COUNT

elandry9840 — Mon, 02 May 2022 20:25:26 GMT

Replied to above

Re: Problems with COUNT

tamerj1 — Mon, 02 May 2022 20:37:19 GMT

elandry9840

Would you please add the moving average measure and snother measure: COUNTROWS ( 'PriceTable')

Re: Problems with COUNT

elandry9840 — Mon, 02 May 2022 20:49:22 GMT

Thank you so much for the help! I have to step out for an hour, but will be back after that and will reply promptly to whatever you need.

Re: Problems with COUNT

tamerj1 — Tue, 03 May 2022 06:47:21 GMT

elandry9840

I apologize as well. It was too late and I fell asleep.
Are you sure you have a relationship. The results are not filtered at all. There is something wrong here

Re: Problems with COUNT

elandry9840 — Tue, 03 May 2022 14:36:31 GMT

Thanks. I went back and deleted them then recreated them. Still no luck.

Re: Problems with COUNT

tamerj1 — Tue, 03 May 2022 15:24:34 GMT

If possible, can you share a sample file?

Re: Problems with COUNT

elandry9840 — Tue, 03 May 2022 17:16:48 GMT

Sure. How do I do that?

Re: Problems with COUNT

tamerj1 — Tue, 03 May 2022 17:44:25 GMT

You can upload for example to We Transfer, DropBox or OneDrive and share the link

Re: Problems with COUNT

Anonymous — Thu, 05 May 2022 07:38:53 GMT

Hi elandry9840 ,

Not sure, is the first measure wrong?
Please use this measure and test.

40-week MA = CALCULATE ( AVERAGE ( PriceTable[closeadj] ), FILTER ( 'Calendar', 'Calendar'[Date] <= MAX ( 'Calendar'[Date] ) && 'Calendar'[Date] >= MAX ( 'Calendar'[Date] ) - 200 ) )

Attached PBIX file for reference.

Best Regards,

Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems with it, please feel free to let us know. Thanks a lot!

Re: Problems with COUNT

elandry9840 — Thu, 05 May 2022 16:55:59 GMT

Hi v-cgao-msft,

Appreciate the reply!! I'm not sure it's working tho. Take a look:

40-week MA (New) =

CALCULATE (

AVERAGE ( PriceTable[closeadj] ),

FILTER (

'Calendar',

'Calendar'[Date] <= MAX ( 'Calendar'[Date] )

&& 'Calendar'[Date]

>= MAX ( 'Calendar'[Date] ) - 200

)

40-week MA =

AVERAGEX(

DATESBETWEEN('Calendar'[Date], max('Calendar'[Date]) - 200, max('Calendar'[Date])),

calculate(sum(PriceTable[closeadj]))

)

Looks like [40-Wk MA (New)] is returning the closeadj. See below:

Could it have something to do with the price table having data only once per week? I noticed your price table had dailt prices.

Thanks again!