Struggling to remove filters

[Anonymous]

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.

Can anyone help?

Thanks.

[jgeddes · Accepted solution]

Something like this might work for you...

Measure = 
COUNTROWS(
    FILTER(ALL(TableOne), TableOne[col1] = SELECTEDVALUE(TableOne[col1]))
)

[Anonymous · Accepted solution]

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

[jgeddes · Accepted solution]

Something like this might work for you...

Measure = 
COUNTROWS(
    FILTER(ALL(TableOne), TableOne[col1] = SELECTEDVALUE(TableOne[col1]))
)