topic Re: Filter Not working on Dax in DAX Commands and Tips

Filter Not working on Dax

Anonymous — Tue, 31 Oct 2023 10:13:32 GMT

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.

Re: Filter Not working on Dax

lbendlin — Sat, 04 Nov 2023 01:31:23 GMT

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

Re: Filter Not working on Dax

Anonymous — Mon, 06 Nov 2023 05:25:38 GMT

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

Re: Filter Not working on Dax

Anonymous — Tue, 07 Nov 2023 07:21:09 GMT

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

Re: Filter Not working on Dax

lbendlin — Tue, 07 Nov 2023 01:56:12 GMT

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.

Re: Filter Not working on Dax

Anonymous — Tue, 07 Nov 2023 07:29:48 GMT

Hi Thanks for working over my issue.

But still the issue is not solved.

The requirement is that based on month selection we want only those Site where the sales continously went down.

You can try diffreent approach aswell.

For an example - suppose i selected June 23 to Oct 23 so total month will be 5, so we want only those site where continously sales went down in these selected month.

Let say there is a site 123 and sales of month on month

Month Sales
June 23 100

July 23 90

Aug 23 80

Sep 23 75

Oct 23 72

Here as we can see the sales is continously down so we need to only find these kinds of site based on month selection.

Mean the result should be filtered out dynamically based on number of month selected.
if selected 5 month then need to show those site where 5 times continously sales went down, if selected 6 month or 8 month then need to show only those site where continously 6 times or 8 times sales went down.

Kindly help me here so that i can come out from this strange challenge.

Thanks in Advance!!

Re: Filter Not working on Dax

lbendlin — Tue, 07 Nov 2023 11:23:11 GMT

Your sample data doesn't support the premise. Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

Re: Filter Not working on Dax

Anonymous — Wed, 08 Nov 2023 05:33:23 GMT

This PBIX file is for report server.

i am again clarifying my issue:-

-I need to identify those site where the sales are going continuous down over a specified period, based on the monthly selection.
For instanceif I select six months, I want to find those sites where sales have consistently decreased for all six of those months, resulting in a count of six consecutive decreases.

But with this count 6 i am also getting those site which is continously down sales for these selected month 5 times, 4 times, 3 times or 2 times or 1 times.

So here we need filteration if i select 6 month then only those site need to show where sitecount is 6 others need to filter which is not happening.

Also i am giving you the data file to get this load in your own premises and check-

https://docs.google.com/spreadsheets/d/1J0St32wZ3ZqjlQJ8zMkGApAhN8Kl8Zuq/edit?usp=drive_link&ouid=104348883861930142596&rtpof=true&sd=true

If there is any issue to understand or maybe i am not clear, we can connect over a sort call to discuss this.

Thanks for your support!!

Re: Filter Not working on Dax

Anonymous — Wed, 08 Nov 2023 05:48:46 GMT

So what i have done so far here......

I have Month/year, Sales, Regionhead(which we are not using as of now in this scenario) .
-Since i have Sales so i calculated the previousmonth sale for site wise using measure
-Now i have compared site currentsales Vs Previousmonth sale and if currentmonth sale<Previousmonth sale then i have kept them as a degrow site.

-Later on i used this as filter pane and only filtered those site where the sales is down compare to previos month

-Now suppose i have selected 6 month from month/year slicer then in table chart i will have only 6 month data and since i have filtered only those site where sales is down compare to last month in filter pane so now i can count the site.

-If any site is 6 times avaialble in this table chart mean that is what i am looking for-- continous sale is down

- that's why i used the site_count dax function which is aplicable to count the site in that table chart only

-Now in selection of any month i can get to know how many month i have selecetd and now i want those site where the countmonth=count_site

-Here i have got the result also but the main issue is that i am getting those site where sales is down for all month

In Summarize if you will see in my PBIX file so i have selected 6 month and those sites you can check in table chart where the site_count is showing 6 those are my result which is 100% correct but if you scroll down then you are getting site_count with 5, 4, 3,2,1 so we want to exclude these sites only because we have selected 6 month so want only those sites where sales down for 6 times.

Same i will select 5 times or 8 times then only those site want where sales down for 5 or 8 times rest we want to exclude.

Requesting to provide your available time to connect so that we can discuss and get a proper resoultion over this.

Thanks again to lookingup this challenging issue.

Re: Filter Not working on Dax

lbendlin — Wed, 08 Nov 2023 12:37:20 GMT

I don't have a test setup for Report Server. I hope someone else can help you further.

Re: Filter Not working on Dax

Anonymous — Thu, 09 Nov 2023 07:53:05 GMT

I Think you need not to do anything extra.
You can get the data and also can load into power BI desktop and then you can try your own method.

Because it's seems very complex to me. I think if you will try may be you can get the desired output.