topic Re: Multiple fact tables - A table of multiple values was supplied where a single value was expected in DAX Commands and Tips

Multiple fact tables - A table of multiple values was supplied where a single value was expected

LydiaRademaker — Fri, 28 Jun 2019 21:55:01 GMT

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

Re: Multiple fact tables - A table of multiple values was supplied where a single value was expected

Anonymous — Mon, 01 Jul 2019 09:23:56 GMT

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.

Re: Multiple fact tables - A table of multiple values was supplied where a single value was expected

LydiaRademaker — Wed, 03 Jul 2019 12:10:12 GMT

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.

Re: Multiple fact tables - A table of multiple values was supplied where a single value was expected

LydiaRademaker — Thu, 11 Jul 2019 20:23:49 GMT

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?

Re: Multiple fact tables - A table of multiple values was supplied where a single value was expected

Anonymous — Fri, 12 Jul 2019 09:51:27 GMT

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