Forum Discussion
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
- AnonymousNot applicable
Hi
Please find the atttached PBIX sample file link to get the data and the requirement already i have mentioned above.
Please feel free to revert in case of anything you required.
Thanks in advance for helping me out of this challending requirement.
https://drive.google.com/file/d/1tu3SSUGGguV5SSWwCDDh5OhVxXEo4c_p/view?usp=drive_link - lbendlin
Super User
Use SUMMARIZECOLUMNS, not SUMMARIZE.
Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523- AnonymousNot applicable
Hi
Please find the atttached PBIX sample file link to get the data and the requirement already i have mentioned above.
Please feel free to revert in case of anything you required.
Thanks in advance for helping me out of this challenging requirement.
https://drive.google.com/file/d/16OM7wFp84AU0a_wktGU6bsMTlv8YxzTB/view?usp=drive_link- lbendlin
Super User
This is much more complex than it may seem. Here is a proposal for a part graphical part code solution using Sparklines and array sorting and switch elimination.
This will work both on the store level and the region head level.