Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
LydiaRademaker
Regular Visitor

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

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.

data modeldata model

I would like to being able to filter on all the products being sold on the same location, with a slicer:

slicer locationslicer location

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

 

4 REPLIES 4
sreenathv
Solution Sage
Solution Sage

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

 

ProductCompetingProduct
AD
BE
CF

 

Table 2: ProductDetails

ProductPriceLocationLabelDateAmount
A100USAABC01-07-2019800
A100UKABC01-07-20192000
A100FranceABC01-07-20191200
A100ItalyABC01-07-2019500
B150USAABC01-07-20191350
B150UKABC01-07-20192250
B150FranceABC01-07-20191200
B150ItalyABC01-07-2019450
C200USAABC01-07-20192400
C200UKABC01-07-20191200
C200FranceABC01-07-20194000
C200ItalyABC01-07-20193200

 

Table 3: CompetingProductDetails

 

ProductPriceLocationLabelDateAmount
D103USAABC01-07-20191854
D108FranceABC01-07-2019540
E150UKABC01-07-2019750
E151ItalyABC01-07-20192416
F193UKABC01-07-20191158
F203FranceABC01-07-2019812
D105South AfricaABC01-07-2019315
E110South AfricaABC01-07-20192090
F125South AfricaABC01-07-20191875

 

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 @sreenathv 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? 

Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors