Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 Output
1 A1 Y 1 3
1 A2 N 1 3
2 A3 Y 2 3
2 A4 Y 2 3
I am using the following formula
Total Business Investment Products =
CALCULATE (
DISTINCTCOUNTNOBLANK ( 'Fact'[AccountNumber] ),
FILTER ( 'Fact', 'Fact'[Flag] = "Y" ),
ALLEXCEPT ( 'Fact', 'Fact'[CustomerNumber] )
)
Solved! Go to Solution.
Hello @Anonymous Give this a try
Total Business Investment Products =
CALCULATE (
DISTINCTCOUNT ( 'Fact'[AccountNumber] ),
ALLEXCEPT ( 'Fact', 'Fact'[CustomerNumber] ),
'Fact'[Flag] = "Y"
)
Hi @Anonymous ,
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.
Best Regards
Icey
Hello @Anonymous Give this a try
Total Business Investment Products =
CALCULATE (
DISTINCTCOUNT ( 'Fact'[AccountNumber] ),
ALLEXCEPT ( 'Fact', 'Fact'[CustomerNumber] ),
'Fact'[Flag] = "Y"
)
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |