How to filter a measure

[serky]

I just cannot work out how to do this so any help would be appreciated.

I have an account table (dim table): account name, start date, end date.
The user chooses a year using a disconnected 'year' slicer.
A measure [account status] calculates whether the account is 'active' or 'inactive' depending on the choice of 'year'.

This all works properly.

How do I create a report table showing only the 'active' accounts for the chosen 'year'?

I have tried a powerpivot table with account name, start date and end date in the row field, and the [account status] in the 'values' field, but I cannot filter 'values' to only show 'active' (using teh 'values filter' in the rows filed does not work with text, and I would prefer to use a slicer rather than the user having to faff around with filters on a pivot table).

Should I be using a powerpivot table or a different method?

Thanks

 

[Peter_SSs]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: https://www.myonlinetraininghub.com/excel-forum/power-pivot/how-to-filter-a-measure
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[serky]

I was intending to update this post with the cross post details but didn't get a chance thanks to internet problems. I have not posted else where.

I have asked exactly the same question at 'myonlinetraininghub' but included an example excel file. I could not see how to upload a file on this forum.

 

[Peter_SSs]

I was intending to update this post with the cross post details but didn't get a chance thanks to internet problems.

Fair enough.

I could not see how to upload a file on this forum.

You can't upload an actual file. You can post small sample mini-sheets with XL2BB - see the link in my signature block below.
It is a good idea to include your expected results on the mini-sheet and explain your requirement in relation to it.

BTW, I also suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)