I have a table with 3 columns, Customer Number, Account Number & Flag. I want to create a calculated column(NOT A MEASURE) which counts the number of accounts per customer when the flag is set to yes.CustomerNumber AccountNumber Flag ExpectedOutput Actual Output1 A1 Y 1 31 A2 N 1 32 A3 Y 2 32 A4 Y 2 3I am using the following formulaTotal Business Investment Products =CALCULATE (DISTINCTCOUNTNOBLANK ( 'Fact'[AccountNumber] ),FILTER ( 'Fact', 'Fact'[Flag] = "Y" ),ALLEXCEPT ( 'Fact', 'Fact'[CustomerNumber] ))
Go to Solution.
Hello @mnayar Give this a try
Total Business Investment Products =
DISTINCTCOUNT ( 'Fact'[AccountNumber] ),
ALLEXCEPT ( 'Fact', 'Fact'[CustomerNumber] ),
'Fact'[Flag] = "Y"
View solution in original post
@jdbuchanan71 This works but when I use it as Filter('Fact','Fact'[Flag])='Y') it doesnt work? Do you know why?
I believe the ALLEXCEPT is removing the fitler added in the FILTER statement.
Hi @mnayar ,
Is this problem solved?
If it is solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.
If not, please let me know.
Kudos to you if you earned one of these! Check your inbox for a notification.
Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.
Find out where you can attend!