Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Filter Not working on Dax

Dear Team, 

 

I have created one table using Summarize and my main data source is coming from the azure synapse to power BI using import mode.

 

Then from their i have summarized a table and below things i have summarized :
1. Month Year

2. SiteID

3. Total Sales

4. Region head

 

Then from there with Total Sales i used dax to get Previous Month sales.

 

 

My Motive was to find out those SiteID which was cointnously sales going down month on month based on month selection.

 

Suppose i have selected 4 month or 6 month then i need those SiteID which continously sales gone down 4 month or 6 month so i need to find out those SiteID as per my business requirement.

 

 

Now what i have done here, since  i was having Total Sales month on month for each siteId so i have created below measure to get things:-

 

1. Previous Month sales  : (Previous Period Sale(AShish) = CALCULATE(SUM(Ashish_Degrow_Sites[Total Sales]), PREVIOUSMONTH(Ashish_Degrow_Sites[Month/Year]))

2.Sales Decrease : IF(SUM(Ashish_Degrow_Sites[Total Sales])< [Previous Period Sale(AShish)] , 1, 0) --> TO find out those site Id which are going down 

 

After that i used that measure in filter pane and selected only 1 so that only those siteId will show where the sales went down

 

3. Count of SiteID Repeation: CALCULATE(COUNTROWS(Ashish_Degrow_Sites), ALLSELECTED(Ashish_Degrow_Sites[Month/Year]))
To check that which siteId is how many time presented in my table.

 

4.Count_OF_Month = DATEDIFF( MIN(Ashish_Degrow_Sites[Month/Year]), MAX(Ashish_Degrow_Sites[Month/Year]), MONTH)+1
This dax will help us to know that how many months we have selected.

 

 

Now the approach i was using that suppose i have selected 4 month then count_of_Month measure will tell us the result as 4 and after using countOf siteId we will get which siteID is how many times available in my table.

 

So if 4 month is matching with 4 (i;e , will filter only those siteID which is went down 4 times) then we will filter that.

 

but here i am facing issue that i am not able to filter those siteID 

 

 

I have tried filter function, calculate function, rank function to get the details but still i was not able to do the same.

 

Could anyone please able to assist me it's very urgent. 

10 Replies