Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Saad L.]

Hello there :), i’m a new user of power bi so please be forgiving with me

I have two fact tables:
– ‘CompanyXSales’ containing the sales of a company X
– ‘MarketSales’ containing the sales of all the companies on the market

I would like to have the percentage of company X sales on the market. The problem is that those two tables doesn’t have a common unique key, but they have some common columns like (date, amount, country)

I was thinking, why not add a column in the ‘MarketSales’ table that will contain ‘True’ if the different values (date, amount, country) does mach with the ones on the ‘MarketSales’ table. but i don’t know how to do that, please help me

otherwise, i could tinker with something with a Python Script…

Please check out the excel file, and Thank you in advance.

——————————
Saad L.
——————————

[Vincent Lacomme]

Hi Saad,

Enclosed is a PBIX with a column “Exist” where I added a DAX formula to check a matching entry in the MarketSales Table.

Hope that helps !

——————————
Vincent L.
Chartered accountant – Expert-comptable
——————————
——————————————-

[Saad L.]

Thank you for your time Vincent,

So you went in the query editor, clicked at the add column pannel then you choose conditional column or you used a function ?

Please if you have any tutorials on using those kind of advanced stuff i’ll be greatfull.

——————————
Saad L.
——————————
——————————————-

[Vincent Lacomme]

Hi Saad,

I didn’t use the PowerQuery editor for that matter.

I went in the Power BI table and added a column with the following formula which is an equivalent to a COUNTIFS in excel :

Exist = COALESCE(CALCULATE(

COUNTROWS(MarketSales);

FILTER(MarketSales;MarketSales[Country]=CompanyX[Country] && CompanyX[Date]=MarketSales[Date] && CompanyX[Amount]=MarketSales[Amount])

);0)

There are a lot of tutorials on the web like these : https://theexcelclub.com/dax-filter-inside-of-calculate/ https://www.red-gate.com/simple-talk/sql/bi/using-the-filter-function-in-dax/

——————————
Vincent L.
Chartered accountant – Expert-comptable
——————————
——————————————-

[Saad L.]

Thank you Vincent, you saved me a lot of time.

I wish you the best !

——————————
Saad L.
——————————
——————————————-

[aKris Tyler]

If your three conditionals are not in the same table, you won’t be able to do that in Power Query unless you merge the tables there first. The Dax method is the next best. I prefer to do most scalar computations in Power Query, as that seems to run faster overall. I join the tables via the Merge, then do conditional columns via the Add column menu. From there, you can do a complex conditional column.

——————————
aKris Tyler
Data Analyst
Ames IA
——————————
——————————————-