The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 |
---|---|
26 | |
12 | |
8 | |
7 | |
5 |
User | Count |
---|---|
28 | |
13 | |
12 | |
12 | |
6 |