Return list of related country based on slicer selection

[Anonymous]

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.

[Anonymous]

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.

[Anonymous]

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.

[mark_endicott]

@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. 

[Anonymous]

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.

[mark_endicott]

@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. 

[Anonymous]

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.

[mark_endicott]

@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.