Select Row Values by column Selection

[sammy02hk]

Hi there, could you please give me some hints on the following scenario... Thank you very much.

Table info:
Promotion Table

Promotion Name and promtion realted Article are not fixed.

Unique Promotion Filter:

Rretrieve by the columns name of Promotion Table, except ArticleNo

Example

If I filter the promotion, how can i return a table with related artice No:

If I select Promotion Name A & F. Article No A(1,2,8,11) and F( 1 & 7) will be returned.

So that i can sum the sales by selected promotion.

Thank you very much.

[amitchandak · Accepted solution]

@sammy02hk , you have to unpivot the first table and bring Promotion Name on rows and then you can join and filter

Unpivot Data(Power Query): https://youtu.be/2HjkBtxSM0g

https://radacad.com/pivot-and-unpivot-with-power-bi

[Riddhesh · Accepted solution]

Hi @sammy02hk,  

Did you intend to accomplish this task?

If so, utilizing a many-to-many relationship between the Article Sales and Promotion Table is an option. I am attaching the modeling view for your reference here.

Warm regards,

Riddhesh

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

[Riddhesh · Accepted solution]

Hi @sammy02hk,  

Did you intend to accomplish this task?

If so, utilizing a many-to-many relationship between the Article Sales and Promotion Table is an option. I am attaching the modeling view for your reference here.

Warm regards,

Riddhesh

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

[sammy02hk · Accepted solution]

Hi @Riddhesh 

Thank you for your reply.

For the promotion table, is it impossible to stay in this form. 

And need to transform to his format?

Since @the Database, Article No and Promtion No are in great amounts, I try to reduce the database size.

Thank you.

[Riddhesh]

Hello @sammy02hk,

Yes, you need to transform your promotion table. It's recommended to organize your data model by separating it into fact tables and dimension tables. The amount of data should not be an issue as long as the database is properly designed. A database is capable of managing a large volume of data, including millions of rows.

Additionally, a well-structured data model is crucial for optimal performance in Power BI.

Thank you

[sammy02hk]

Thank you for you help.

[sammy02hk]

@amitchandak  Hi Amit. Thank you for your solution.
As the amount of Promotion table is quite a lot. Are there any alternative way, so that I can reduce the data size. 

Thank you.

[amitchandak · Accepted solution]

@sammy02hk , you have to unpivot the first table and bring Promotion Name on rows and then you can join and filter

Unpivot Data(Power Query): https://youtu.be/2HjkBtxSM0g

https://radacad.com/pivot-and-unpivot-with-power-bi

[sammy02hk]

Hello Amit,

Thank you for your reply. Let me study this function.