topic Re: How to ignore a slicer for one measure , but apply it on others in DAX Commands and Tips

How to ignore a slicer for one measure , but apply it on others

ravikora — Sun, 22 Nov 2020 14:20:10 GMT

Hi all,

Required to keep measure (AvgLTD) values unchanged/constant in that row filter context, regardless of slicer selection (Period).

I was actually already tried with REMOVEFILTERS() and ALL dax functions but not worked.

Below are tables to understand everyone on my requirement.

Table 1- DIM

Id, Date, Period
1 1/1/2020 Jan
2 1/2/2020 Feb
3 1/3/2020 Mar
4 1/4/2020 Apr

Table 2 - FACT

Period_Id, Name, TotalSales, AverageSales(TotalSales/SlicerSelectionCount), AvgLTD(Totalsales/TotalPeriodCount)
1 Product1 120 60 30
2 Product2 400 200 100
3 Product3 30 15 7.5
4 Product4 200 100 50

Slicer used to filter data:

Period
Jan
Feb
Mar
Apr

1. Both Table 1 and 2 has inner join on 'Id' and 'Period_Id'

2. In this sample 'AverageSales' are calucalted for 'Jan-Feb' period selection from slicer.

3. 'AvgLTD' calculated on 'TotalSales/TotalPeriodCount'

Can anyone please help me for solution?

Thanks,

Ravi

Re: How to ignore a slicer for one measure , but apply it on others

amitchandak — Mon, 23 Nov 2020 02:06:24 GMT

ravikora ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Try a measure like

calculate(divide([TotalSales],[TotalPeriodCount]), allexcept(Table, Table[product]))

Re: How to ignore a slicer for one measure , but apply it on others

wdx223_Daniel — Mon, 23 Nov 2020 05:34:40 GMT

ravikora =[TotalSales]/calculate(distinctcount(dimtable[period]),all(dimtable))

Re: How to ignore a slicer for one measure , but apply it on others

mohammedadnant — Mon, 23 Nov 2020 09:20:55 GMT

Hi ravikora ,

Good day,

Try allexcept DAX, for clarification pls check this video

https://youtu.be/QDiyN4fUsds

Thanks & Regards,

Mohammed Adnan

www.youtube.com/taik18

Re: How to ignore a slicer for one measure , but apply it on others

ravikora — Mon, 23 Nov 2020 11:36:28 GMT

Thanks amitchandak

dax worked well and i was not used it before in proper way. Finally done with solution. Thanks once again!

calculate(divide([TotalSales],[TotalPeriodCount]), allexcept(Table, Table[product]))

Cheers,

Ravi

Re: How to ignore a slicer for one measure , but apply it on others

ravikora — Mon, 23 Nov 2020 11:38:27 GMT

Thanks wdx223_Daniel

Re: How to ignore a slicer for one measure , but apply it on others

ravikora — Mon, 23 Nov 2020 11:39:15 GMT

mohammedadnant i will see.. thanks very much.