topic Re: Select Row Values by column Selection in DAX Commands and Tips

Select Row Values by column Selection

sammy02hk — Tue, 10 Jan 2023 09:41:09 GMT

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.

Re: Select Row Values by column Selection

amitchandak — Tue, 10 Jan 2023 09:49:28 GMT

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

Re: Select Row Values by column Selection

sammy02hk — Tue, 10 Jan 2023 10:08:07 GMT

Hello Amit,

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

Re: Select Row Values by column Selection

sammy02hk — Tue, 10 Jan 2023 13:32:41 GMT

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.

Re: Select Row Values by column Selection

Riddhesh — Tue, 10 Jan 2023 13:35:57 GMT

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.

Re: Select Row Values by column Selection

sammy02hk — Wed, 11 Jan 2023 02:59:07 GMT

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.

Re: Select Row Values by column Selection

Riddhesh — Wed, 11 Jan 2023 05:28:14 GMT

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

Re: Select Row Values by column Selection

sammy02hk — Wed, 11 Jan 2023 05:44:52 GMT

Thank you for you help.