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.
With the following data:
table1:
col1 | col2 | col3 |
a | X | 1 |
a | Y | 2 |
a | Z | 3 |
b | Z | 1 |
b | Y | 2 |
table2:
col4 | col5 |
1 | Q |
2 | R |
3 | S |
joined 1 to many on the numeric column:
My desired outcome, with the final column being the count of occurrences in column1:
col1 | co2 | col5 | measure |
a | X | Q | 3 |
a | Y | R | 3 |
a | Z | S | 3 |
b | Z | Q | 2 |
b | Y | R | 2 |
I would also like to filter by column5, so if the user pics Q the following outcome is shown:
col1 | col2 | col5 | measure |
a | X | Q | 3 |
b | Z | Q | 2 |
I have tried a few methods with ALLEXCEPT, ALLSELECTED and REMOVEFILTERS but an struggling to get it correct.
Solved! Go to Solution.
Something like this might work for you...
Measure =
COUNTROWS(
FILTER(ALL(TableOne), TableOne[col1] = SELECTEDVALUE(TableOne[col1]))
)
Proud to be a Super User! | |
Hi @Anonymous ,
May I ask if your problem has been solved. If the above reply was helpful, you may consider marking it as solution. If the problem is not yet solved, please follow the steps below:
Add new measure:
count =
CALCULATE (
COUNTROWS ( 'Table1' ),
FILTER ( ALL ( 'Table1' ), 'Table1'[col1] = SELECTEDVALUE ( Table1[col1] ) )
)
Final output:
How to Get Your Question Answered Quickly - Microsoft Fabric Community
If it does not help, please provide more details with your desired out put and pbix file without privacy information.
Best Regards,
Ada Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi @Anonymous ,
May I ask if your problem has been solved. If the above reply was helpful, you may consider marking it as solution. If the problem is not yet solved, please follow the steps below:
Add new measure:
count =
CALCULATE (
COUNTROWS ( 'Table1' ),
FILTER ( ALL ( 'Table1' ), 'Table1'[col1] = SELECTEDVALUE ( Table1[col1] ) )
)
Final output:
How to Get Your Question Answered Quickly - Microsoft Fabric Community
If it does not help, please provide more details with your desired out put and pbix file without privacy information.
Best Regards,
Ada Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Something like this might work for you...
Measure =
COUNTROWS(
FILTER(ALL(TableOne), TableOne[col1] = SELECTEDVALUE(TableOne[col1]))
)
Proud to be a Super User! | |
User | Count |
---|---|
21 | |
20 | |
15 | |
10 | |
7 |
User | Count |
---|---|
28 | |
28 | |
12 | |
12 | |
12 |