Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
I would appreciate any assistance with the following scenario. I have a large table (about 11 million rows) and need to compare the counts between different "types", but only wish to compare those in the second group which share the same "account" numbers (like a left join, but it is all one table).
I only want to count the "Written" type if the account listed on the "Written" type rows also exists on a "Lead" type row... so 3 "Written" type rows divided by all "Lead" type rows which would give me 37.5%.
Account | Type |
Acc123 | Lead |
Acc124 | Lead |
Acc125 | Lead |
Acc126 | Lead |
Acc127 | Lead |
Acc128 | Lead |
Acc129 | Lead |
Acc130 | Lead |
Acc123 | Written |
Acc124 | Written |
Acc128 | Written |
Acc132 | Written |
Acc133 | Written |
Acc134 | Written |
Solved! Go to Solution.
Hi @kfortenberry ,
@ThxAlot Thanks for your reply!
And @kfortenberry , here is my sample data:
And you can try this DAX:
Measure =
VAR _Lead =
CALCULATETABLE(
SELECTCOLUMNS(
FILTER(
'Table',
'Table'[Type] = "Lead"
),
"Account", 'Table'[Account]
)
)
VAR _Written =
CALCULATETABLE(
SELECTCOLUMNS(
FILTER(
'Table',
'Table'[Type] = "Written"
),
"Account", 'Table'[Account]
)
)
VAR _Accounts =
INTERSECT(_Written, _Lead)
VAR _COUNT =
COUNTROWS(_Accounts)
VAR _COUNT_Lead =
CALCULATE(
COUNTROWS('Table'),
'Table'[Type] = "Lead"
)
RETURN
_COUNT / _COUNT_Lead
And the final output is as below:
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for the formula. This is mostly working, but I did notice that if a duplicate account number exists in the "Written" data it is not counting the duplicates.
Would you know of how to adjust the formula so that the duplicates would be counted?
Hi @kfortenberry ,
@ThxAlot Thanks for your reply!
And @kfortenberry , here is my sample data:
And you can try this DAX:
Measure =
VAR _Lead =
CALCULATETABLE(
SELECTCOLUMNS(
FILTER(
'Table',
'Table'[Type] = "Lead"
),
"Account", 'Table'[Account]
)
)
VAR _Written =
CALCULATETABLE(
SELECTCOLUMNS(
FILTER(
'Table',
'Table'[Type] = "Written"
),
"Account", 'Table'[Account]
)
)
VAR _Accounts =
INTERSECT(_Written, _Lead)
VAR _COUNT =
COUNTROWS(_Accounts)
VAR _COUNT_Lead =
CALCULATE(
COUNTROWS('Table'),
'Table'[Type] = "Lead"
)
RETURN
_COUNT / _COUNT_Lead
And the final output is as below:
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you! This worked perfectly!
User | Count |
---|---|
123 | |
69 | |
67 | |
58 | |
52 |
User | Count |
---|---|
185 | |
92 | |
67 | |
62 | |
52 |