topic Using SWITCH to filter table and show all if no filter is applied in DAX Commands and Tips

Using SWITCH to filter table and show all if no filter is applied

Anonymous — Mon, 29 Nov 2021 12:11:32 GMT

Hello,

I am trying to write a measure to use as a filter on a table/matrix visual. The purpose of the filter is to filter the visual, if i have selected a value in another visual. But if i have nothing selected i want to show all the item_no where the "post_month_year (sale)" is blank.

This is the measure i have so far:

test2 = SWITCH ( TRUE (), VALUES ( Vareposter_combined[Post_month_year (purchase)] ) = MAX ( 'Post_month_year (Purchase)'[Post_month_year (purchase)] ) && VALUES ( Vareposter_combined[Post_month_Year (sale)] ) >= MAX ( 'Post_month_year (Sale)'[Post_month_Year (sale)] ), 1, ISBLANK ( VALUES ( Vareposter_combined[Post_month_year (sale)] ) ), 1, NOT ( ISFILTERED ( Vareposter_combined[Post_month_year (purchase)] ) ), 1, 0 )

I use the measure like this:

Re: Using SWITCH to filter table and show all if no filter is applied

v-xiaotang — Thu, 02 Dec 2021 06:20:16 GMT

Hi Anonymous

Not sure what's your data. Based on your description, you can create a measure returns 1 when you select a value in another visual, else if you have nothing selected, then it shows all item_no where the "post_month_year (sale)" is blank.

If you need more help, could you provide

(1) a sample file, you can replace raw data with bogus data to protect your privacy,

(2) or provide some samp data involved in the measure

(3) give your expected result based on the sample you provide

Thanks.

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.