topic Re: Return list of related country based on slicer selection in DAX Commands and Tips

Return list of related country based on slicer selection

Anonymous — Fri, 23 Aug 2024 09:51:18 GMT

Hi,

I have model similar to the attached image. I need to write a measure that will show Agreegated Sale when Camp Name is selected using slicer. If user select Camp1,Camp2,Camp3 , it should return the associated Country Code (UK , US,IN) and retrun Sum(Sale) = 85 (10+20+35+20). There are several other slicers as well and few Dimension tables so the measure should consider all those as well while calculating the measure.

Is there a way where I can pass the associated Countries when any slicer is selected.

Re: Return list of related country based on slicer selection

mark_endicott — Fri, 23 Aug 2024 09:54:43 GMT

Anonymous - Rather than solve this through DAX (Complex), why dont you solve this through modelling (easy) and move Camp Name to DimLocation?

You could also move CountryName there, and then you have one dimension for two fact tables, and no complex DAX.

This could easily be acheived by using MergeQueries in Power Query, here is some guidance, https://learn.microsoft.com/en-us/power-query/merge-queries-overview

If this works for you, please accept as the solution, it helps with visibility of the fix for others.

Re: Return list of related country based on slicer selection

Anonymous — Fri, 23 Aug 2024 10:13:59 GMT

CampName is an attribute of Fact table and can be repeated n number of times in Fact table with other key attributes. Moving to to Dim Location wont be possible. Also DimLocation is being used to slice and dice other fact table that are available in my model.

This need to be handled thru DAX only.

Re: Return list of related country based on slicer selection

mark_endicott — Fri, 23 Aug 2024 10:20:31 GMT

Anonymous - Just because it's repeated many times, it does not mean it can't be moved to DimLocation. In fact, according to the principles of Dimensional Modelling and the best practice guidance of using a Star Schema with Power BI it SHOULD be moved to DimLocation.

If this is done, it would be easier to exclude filtering from Camp Name in the DAX of measures from other fact tables (where it should not impact) than to move the filtering through to FactSale with your current solution.

If you wish to handle this with DAX, that's your preference.

Re: Return list of related country based on slicer selection

Anonymous — Mon, 26 Aug 2024 06:44:35 GMT

Hi Anonymous ,

I create four tables as you mentioned.

I think you can create a relationship between DimLocation table and FactSale table.

Then you can create a measure and here is the DAX code.

Total Sales = SUMX ( FILTER ( CROSSJOIN ( 'FactSale', 'DimLocation', 'FactEnquiry' ), 'DimLocation'[CountryCode] = 'FactSale'[CountryCode] && 'FactEnquiry'[Location#] = 'DimLocation'[Location#] ), [Sale] )

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Return list of related country based on slicer selection

Anonymous — Tue, 27 Aug 2024 16:49:06 GMT

Hi Anonymous

Thank you for responding.

This is working fine on smaller dataset. My data volumn is large. It is running out of memory and not returning results when I implemented same in my report.

Re: Return list of related country based on slicer selection

Anonymous — Wed, 28 Aug 2024 02:59:21 GMT

Hi Anonymous ,

This method helps you in the direction in which your image is displayed.

What I'm trying to say is that you can use the virtual table method to solve the problem you mentioned. In the DAX mentioned above, I used the CROSSJOIN method, and I thought you could try it.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Return list of related country based on slicer selection

mark_endicott — Wed, 28 Aug 2024 10:09:34 GMT

Anonymous - This is exactly why I mentioned this should be solved via modelling and not DAX, which will be very inefficient and will lead to several bad modelling practices.

The Bi-directional many-to-many relationship suggested by Yilong Zhou is one of the biggest things you should avoid when working with large data volumes. It forces a full scan of both tables.

I would still suggest you consider moving Camp Name to DimLocation, if you do this with the correct join it will not dupicate the records in DimLocation or you could just use Table.Distinct once you have done so.