topic Struggling to remove filters in DAX Commands and Tips

Struggling to remove filters

Anonymous — Wed, 19 Jun 2024 10:53:14 GMT

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.

Re: Struggling to remove filters

jgeddes — Wed, 19 Jun 2024 13:15:00 GMT

Something like this might work for you...

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

Re: Struggling to remove filters

Anonymous — Tue, 02 Jul 2024 07:27:26 GMT

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