topic Re: Filter solution in DAX Commands and Tips

Filter solution

Arne — Mon, 06 Apr 2020 15:57:50 GMT

Hi!
From the example below i would like to filter sales in period 2020-01 where sales is more than 80. But i would likte to get all periods for companys that have sales of more than 80 in period 2020-01. Desired result should be to see Amazon sales in period 2020-01 and 2020-02 but not microsoft sales. is that possible and how to solve that?

Arne

Example

Company Period Sales

Amazon 2020-01 90

Amazon 2020-02 70

Microsoft 2020-01 75

Microsoft 2020-02 100

Re: Filter solution

Greg_Deckler — Mon, 06 Apr 2020 16:14:39 GMT

So maybe:

FILTER('Table',[Company] = "Amazon" && [Sales] > 80)

Re: Filter solution

Arne — Tue, 07 Apr 2020 05:49:47 GMT

Hi Greg!

In the example it would work but in my solution i,ve got hundreds of departments so i would need some solution that first filters out every department that has sales over a specific limit for a selected period and then i would like to see sales trends for these companys for the periods i choose. So it,s really a two step issue.

1. Find companys that qualify for selected sales level for the period i choose.

2. For companys that qualify for the first criteria show sales level for the period in first critera but also for other chosen periods.

Br Arne

Re: Filter solution

camargos88 — Tue, 07 Apr 2020 18:41:06 GMT

Hi Arne ,

Try this measure:

Measure 2 =

VAR _company = SELECTCOLUMNS(FILTER(ALL('Table (3)'); 'Table (3)'[Sales] > 80 && 'Table (3)'[Period] = DATE(2020; 1;1)); "Company"; 'Table (3)'[Company])

RETURN SUMX(FILTER('Table (3)'; 'Table (3)'[Company] IN _company); 'Table (3)'[Sales])

Ricardo