topic Re: Create/Join to a new table from two tables with no related fields in DAX Commands and Tips

Create/Join to a new table from two tables with no related fields

MosheLP — Wed, 10 Nov 2021 17:59:35 GMT

Hi PBI'ers,

I'm stumped , several hours later 😞 how do I do the below in DAX:
Solved: Join/Pivot two tables with no relations? - Microsoft Power BI Community

Ideally there would be a row for each "date" and "name" in their respective columns instead.

Re: Create/Join to a new table from two tables with no related fields

AlexisOlson — Wed, 10 Nov 2021 18:16:48 GMT

There's not a nice way to pivot with DAX. You have to define a new column manually for each one you want. See here for example: https://community.powerbi.com/t5/Quick-Measures-Gallery/DAX-Unpivot/td-p/574832

On the other hand, you very rarely actually need to do this in order to achieve your ultimate goal since DAX handles unpivoted data nicely. What's the reasoning behind doing this? Are you after a certain visual or calculation or what? Are you actually looking for the pivoted shape or is an unpivoted crossjoin that you can then pivot in a matrix visual OK too?

Re: Create/Join to a new table from two tables with no related fields

MosheLP — Thu, 11 Nov 2021 05:17:03 GMT

Thanks for your reply, much appreciated.

My goal is to create a bar race chart, requiring me to show clusters of users' (e.g. employees in a Department) product consumption percentage over a period of months i.e. which department has the highest product consumption percentage from it's employees.

So i have two tables, one with the users in their clusters and the second table being the product consumption by user for those who have consumed a product.

Therefor my intention is to aggregate the purchases by department into a new table so as to obtain the percentage value for product consumption in a department. After which I will then need to accrue(running total) the product consumption aggregate for each department by month.

I have added an excel e.g that can hopefully explain better:

Re: Create/Join to a new table from two tables with no related fields

AlexisOlson — Thu, 11 Nov 2021 15:33:51 GMT

This is definitely doable. If you share a file or something I can copy & paste from, I can probably help but I'm not keen to retype data from a screenshot.

Re: Create/Join to a new table from two tables with no related fields

MosheLP — Thu, 11 Nov 2021 15:46:43 GMT

Please see below:

People Data Table

Employee Name	Department Name
John	Finance
Mary	Finance
Diana	Finance
Mark	IT
Tom	IT
Harry	IT
Sandra	IT
Sam	HR
Henry	HR

Product consumption
Product Name	Employee Name	Department Name	Month
MS Word	John	Finance	May
PowerPoint	Henry	HR	May
Excel	Mark	IT	May
Outlook	Mark	IT	June
MS Word	Harry	IT	June
PowerPoint	Sam	HR	July
PowerPoint	Harry	IT	July
Excel	Henry	HR	July
Outlook	Mary	Finance	July
Excel	Tom	IT	August
MS Word	Henry	HR	August

Desired Result
Period	Department	distinct employees	Percentage
May	Finance	1	33%
May	HR	1	33%
May	IT	1	25%
June	IT	2	50%
July	HR	1	50%
July	IT	3	75%
July	Finance	2	67%
August	IT	3	75%
August	HR	1	50%

Hope this helps, and thank you in advance.

Re: Create/Join to a new table from two tables with no related fields

AlexisOlson — Thu, 11 Nov 2021 16:21:00 GMT

I don't understand where some of your numbers are coming from (e.g. how can there be a count of 3 in August when there are only two rows?) but I think something like this should get you closer to what you're after:

Desired = ADDCOLUMNS ( SUMMARIZE ( 'Product', 'Product'[Month], 'Product'[Department Name], "Distinct Employees", DISTINCTCOUNT ( 'Product'[Employee Name] ) ), "Percentage", DIVIDE ( [Distinct Employees], COUNTROWS ( FILTER ( People, People[Department Name] = EARLIER ( 'Product'[Department Name] ) ) ) ) )

Re: Create/Join to a new table from two tables with no related fields

MosheLP — Sun, 14 Nov 2021 12:20:22 GMT

Hi Alexis,

I gave it a test and it's almost there.

This missing part is just the values accruing which is already done in the percentage column as an example. So I realise that the EARLIER function has large to play in this but I have had no luck since my tests.

I also realise the results table can be somewhat confusing as I manually entered the results I am looking to achieve. Each month that follows is meant to take into account activity from the previous one meaning August should also include Finance in it's running total together with the accrued distinct employees for it's department.

That is why there are 3 employees in for IT in August as Mark, Harry and Tom consumed their products over these three months. I hope that clears up the confusion and I am sincerely grateful.

If you kindly can refer to my results table

Re: Create/Join to a new table from two tables with no related fields

v-robertq-msft — Mon, 15 Nov 2021 08:22:16 GMT

Hi, MosheLP

According to your description, I can roughly understand your requirement, but I still can’t understand the logic of your two columns [distinct employees] and [Percentage].

You said that there are 3 employees in for IT in August as Mark, Harry, and Tom, but for HR why there is only 1 employee in? Henry and Sam are in in the recent 3 months based on your logic.

For the column [Percentage], would you like to explain the denominator of it in detail?

How to Get Your Question Answered Quickly

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

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

Re: Create/Join to a new table from two tables with no related fields

MosheLP — Mon, 15 Nov 2021 08:49:14 GMT

Hi and thank you, you are correct, sadly I am doing this manually.

It should look like:

Period	Department	Distinct Employees	Percentage
May	Finance	1	33%
May	HR	1	33%
May	IT	1	25%
June	IT	2	50%
July	HR	2	100%
July	IT	3	75%
July	Finance	2	67%
August	IT	3	75%
August	HR	2	100%
August	Finance	2	67%

The denominator is the total employees in the department.

Re: Create/Join to a new table from two tables with no related fields

v-robertq-msft — Tue, 16 Nov 2021 08:35:10 GMT

Hi, MosheLP

According to your description and sample test data, I can now roughly understand your requirement, but I still think the percentage value for the May HR is incorrect because the HR department only has two employees in your test data, it should be 50% am I right?

Based on your logic, I figured out a method to achieve this, you can follow my steps:

Create a calculated column in the table ‘Product consumption’ to get the month number:

Month number = var _number= SWITCH([MONTH],"August",08,"December",12,"July",07,"November",11,"October",10,"September",09,"April",04,"February",02,"January",01,"March",03,"May",05,"June",06) return VALUE(_number)

Then create two measures like this:

Distinct Employees = CALCULATE(DISTINCTCOUNT('Product consumption'[Employee Name]),FILTER(ALL('Product consumption'),[Month number]<=MAX('Product consumption'[Month number])&&[Month number]>=MAX('Product consumption'[Month number])-2&&[Department Name]=MAX('Product consumption'[Department Name])))

Percentage = var _count=COUNTX(FILTER(ALL('People Data Table'),[Department Name]=MAX('Product consumption'[Department Name])),[Employee Name]) return divide([Distinct Employees],_count)

Then set the measure format of [Percentage] to percentage:

Then create a table chart and place it like this:

And you can get what you want.

You can download my test pbix file below

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

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