Need Power Pivot Table in Excel that Counts Rows based on Date Table Filter

[Txtcher]

Not sure if this is the correct forum to post this, but here it goes:

I have a very, very simple model with the following tables:

- HX 

- MA Apps - has one-to-many relationship with HX:  'MA Apps'[Id] - HX[ParentId]

- Calendar - has a one-to-many relationship with HX[Date] in the HX table. - FYI, I marked it as the Calendar table.

In the HX table, I have the following measure:  

Approved:=CALCULATE(
    Min(HX[HXDate]),HX[NewValue] = "Approved")

I want to create a pivot table that counts the number of MA Apps ([Id]) approved based on a slicer from Calendar. 

In the Power BI report  I used this measure to get the row count for MA Apps and it works great:

MA Apps Approved:=COUNTROWS(
	SUMMARIZE(
		FILTER('MA Apps', [Approved]),
	'MA Apps'[Id])
)

But this measure does not work in Excel's power pivot. The data blanks out and it tells me relationships between tables may be needed.

Row Context, Filter Context, Context Transition....gah! I've watched videos, googled, read posts, but can't figure out how to make this work in Excel's power pivot. 

Can someone put me out of my misery?

Thanks in advance.

[v-ssriganesh]

Hi @Txtcher,
Thank you for reaching out to the Microsoft fabric community forum. 

It looks like your issue is related to an Excel query, and for the best possible support on this, I would recommend posting your query in the Excel Community. The community there is specifically focused on Excel related issues, and you'll likely get the most accurate solution for your needs.

You can find the Excel Community here: Microsoft Excel Community

Just to clarify, the Microsoft Fabric Community focuses on issues related to Power BI and Microsoft Fabric, so the Excel Community will be the best place for your specific query.

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[jxmb · Accepted solution]

check the direction between MA Apps and HX table, Calendar table can`t filter MA Apps for the many to one relation between MA Apps and HX.

[Txtcher]

Hello,

Thank you for the reminder to check the relationship. The direction was correct, but I had selected the wrong field! It is mistakes like this one that really make you think you are losing your mind. Especially when you are new and learning.

Thanks again. Problem solved.