cancel
Showing results for
Did you mean:
Highlighted
Helper I

## sets of products in fact sale table / data model and DAX

Hi there,

I have a simple model with a table of sales facts and a table of product dimensions. Some of the products are actually sets of products.

In the fact table they appear as one item with its own quantity and value.

 transaction id product id quantity value 1 product1 6 60 1 product2 6 90 2 product1 12 120 3 set1 1 70

In the dimension table, they appear as a separate item.

 product price product1 10 product2 15 set1 70

In addition to these two tables, there is an additional table that defines the compositions of the set, i.e. the type and amount of products that make up the set.

 sets parts of set quantity in set set1 product1 3 set1 product2 3

My question is how to add to the model this third table with the definition of sets, so that the DAX function can report the amounts and sales values of products and products that are included in the set (so, only products specified in dim table, not sets)?

And how to prepare a DAX function that counts only the products and products included in the set ( quantity in set [sets] * quantity [transactions] ), not products and sets.

In addition, how to prepare a function that calculates the value of individual products after "breaking" the sets? (based on the proportions of prices of products that make up the set)

5 REPLIES 5
Highlighted
Community Support

## Re: sets of products in fact sale table / data model and DAX

hi, @Hermes

And I think you should create a relationship between dimension table and additional table by product id and sets.

Then create a measure by IF conditional.

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper I

## Re: sets of products in fact sale table / data model and DAX

Hello,

My current output looks like this:

 product name transaction count sum of quantity sum of value product1 2 18 180 product2 1 6 90 set1 1 1 70 sum 3 25 340

My desired output should look like this (no sets in product name field):

 product name transaction count sum of quantity sum of value product1 3 21 (18+3 from set) 208 (180 + 28*) product2 2 9 (6+3 from set) 132 (90 + 42*) sum 3 30 340

* calculation is based on share of value in product set, show in updated set table below:

 sets parts of set quantity in set price from products value of prd in set value of set share of value set1 product1 3 10 30 75 0,4 set1 product2 3 15 45 75 0,6

value per product calculated like this:

set value from value[transaction] * share of value[sets table]

Highlighted
Community Support

## Re: sets of products in fact sale table / data model and DAX

hi, @Hermes

For your requirement(no sets in product name field), it's better to merge the fact table and additional table.

If you want to do it by dax, you also need to create a new "merge" table by dax.

For example, use this formula to add a new table

`Table = UNION(FILTER(CROSSJOIN('fact',additional),'fact'[product id]=additional[sets]),FILTER(ADDCOLUMNS('fact',"sets",BLANK(),"parts of set",BLANK(),"quantity in set",BLANK(),"price from products",BLANK(),"value of prd in set",BLANK(),"value of set",BLANK(),"share of value",BLANK()),LOOKUPVALUE(additional[sets],additional[sets],'fact'[product id])=BLANK()))`

Then use this formula to create two measrue

`sum of quantity = CALCULATE(SUMX('Table',IF(ISBLANK([quantity in set]),[quantity],[quantity]*[quantity in set])))`
`sum of value = CALCULATE(SUMX('Table',IF(ISBLANK([quantity in set]),[value],[value]*[share of value])))`

Result:

here is pbix file, please try it.

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper I

## Re: sets of products in fact sale table / data model and DAX

Highlighted
Community Support

## Re: sets of products in fact sale table / data model and DAX

hi, @Hermes

For your case, I think this is a great way by dax, since you don't want to show set1 in visual.

Or if you could use merge the tables in Edit Queries?

https://support.office.com/en-us/article/merge-queries-power-query-fd157620-5470-4c0f-b132-7ca2616d1...

Best Regards,

Lin

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

Announcements

#### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

#### Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

#### Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors