Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi there,
I’m trying to filter my results on being on the same location.
My data model exists of one fact table with my product details, where Location is one of the columns.
The product has similar/competing products which we want to compare with. The fact table with similar/competing products also has a column Location.
I would like to being able to filter on all the products being sold on the same location, with a slicer:
This slicer sets a parameter, which I use in a measure called same location. I choose a product, and I would like to know if the competing products are also sold at that location.
Only when the location of all the products that are similar to the selected product are the same, I would like to show them in a visual.
Is there a way to get the filter right without getting the error ‘A table of multiple values was supplied where a single value was expected’? As the product is sold at different locations, as are the competing products, I never seem to be able to get a scalar value.
Anyone any idea how to filter on a column with multiple values, based on a filter on a different fact table?
Or is there an easier way to determine if the products are sold at the same location?
thanks in advance
I was curious to know if your data model will be suitable for such a calculation. So I tried filling some dummy data and tried to calculate the figures. I am getting the results. The following are the sample data and the measures that I have written to calculate the results.
Table 1: ProductLink
Product | CompetingProduct |
A | D |
B | E |
C | F |
Table 2: ProductDetails
Product | Price | Location | Label | Date | Amount |
A | 100 | USA | ABC | 01-07-2019 | 800 |
A | 100 | UK | ABC | 01-07-2019 | 2000 |
A | 100 | France | ABC | 01-07-2019 | 1200 |
A | 100 | Italy | ABC | 01-07-2019 | 500 |
B | 150 | USA | ABC | 01-07-2019 | 1350 |
B | 150 | UK | ABC | 01-07-2019 | 2250 |
B | 150 | France | ABC | 01-07-2019 | 1200 |
B | 150 | Italy | ABC | 01-07-2019 | 450 |
C | 200 | USA | ABC | 01-07-2019 | 2400 |
C | 200 | UK | ABC | 01-07-2019 | 1200 |
C | 200 | France | ABC | 01-07-2019 | 4000 |
C | 200 | Italy | ABC | 01-07-2019 | 3200 |
Table 3: CompetingProductDetails
Product | Price | Location | Label | Date | Amount |
D | 103 | USA | ABC | 01-07-2019 | 1854 |
D | 108 | France | ABC | 01-07-2019 | 540 |
E | 150 | UK | ABC | 01-07-2019 | 750 |
E | 151 | Italy | ABC | 01-07-2019 | 2416 |
F | 193 | UK | ABC | 01-07-2019 | 1158 |
F | 203 | France | ABC | 01-07-2019 | 812 |
D | 105 | South Africa | ABC | 01-07-2019 | 315 |
E | 110 | South Africa | ABC | 01-07-2019 | 2090 |
F | 125 | South Africa | ABC | 01-07-2019 | 1875 |
Measure 1:
ProductSalesSameLocation = CALCULATE ( SUMX ( ProductDetails, ProductDetails[Amount] ), ProductDetails[Location] IN VALUES ( CompetingProductDetails[Location] ) )
Measure 2:
CompetingProductSalesSameLocation = CALCULATE ( SUMX ( CompetingProductDetails, CompetingProductDetails[Amount] ), CompetingProductDetails[Location] IN VALUES ( ProductDetails[Location] ) )
Measure 3:
ProductSalesAllLocations = SUMX ( ALLSELECTED ( ProductDetails ), ProductDetails[Amount] )
Measure 4:
CompetingProductSalesAllLocations = SUMX ( ALLSELECTED ( CompetingProductDetails ), CompetingProductDetails[Amount] )
Try this and let me know if you are not getting the correct results. We can finetune as necessary.
You must use the "Product" field from the "ProductLink" table in your slicer for filtering the product. The measures are written on that assumption.
Thank you so much @Anonymous for the example measures! This helps a lot. In this example I totally get the way to calculate the total amount by filtering on the locations. What I am looking for is somewhat more complicated, as I would like to summarize or count only when all competing products are on the same location; in the example we would have Product A with competing products D and E for example. If D and E are both sold at the USA, as is Product A, then I want to sum the amounts; if only D is also in the USA and E is not, then I don't want to sum these amounts.
It's hard to filter on this when I am trying to apply this in at a visual that contains the data of the competing products, as I am looking at for example Product E and only want to show results of the locations where all competing products are sold.
Is there anyone who can help me out with creating a measure that only counts when all products are on the same location as mentioned in the example above?
First of all, I would - in your place - read about a correct dimensional design. Using M2M in Power BI the way I can see in your picture might not be the best idea. Do you know exactly how this works?
Before you start doing crazy things and before you're not able to figure out what your Dax formulas calculate, please watch this (if you have not yet):
https://www.youtube.com/watch?v=pvIVMEFQokE
Best
Darek