Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MosheLP
Regular Visitor

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

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. 

1 ACCEPTED SOLUTION
v-robertq-msft
Community Support
Community Support

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?

vrobertqmsft_0-1637051608305.png

 

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:

vrobertqmsft_1-1637051608308.png

 

Then create a table chart and place it like this:

vrobertqmsft_2-1637051608313.png

 

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.

View solution in original post

9 REPLIES 9
v-robertq-msft
Community Support
Community Support

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?

vrobertqmsft_0-1637051608305.png

 

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:

vrobertqmsft_1-1637051608308.png

 

Then create a table chart and place it like this:

vrobertqmsft_2-1637051608313.png

 

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.

v-robertq-msft
Community Support
Community Support

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.

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


It should look like:

PeriodDepartmentDistinct EmployeesPercentage
MayFinance133%
MayHR133%
MayIT125%
JuneIT250%
JulyHR2100%
JulyIT375%
JulyFinance267%
AugustIT375%
AugustHR2100%
AugustFinance267%

 

The denominator is the total employees in the department.

AlexisOlson
Super User
Super User

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?

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:eg.JPG

 

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.

 

Please see below:

 

People Data Table
Employee NameDepartment Name
JohnFinance
MaryFinance
DianaFinance
MarkIT
TomIT
HarryIT
SandraIT
SamHR
HenryHR

 

Product consumption
Product NameEmployee NameDepartment NameMonth
MS WordJohnFinanceMay
PowerPointHenryHRMay
ExcelMarkITMay
OutlookMarkITJune
MS WordHarryITJune
PowerPointSamHRJuly
PowerPointHarryITJuly
ExcelHenryHRJuly
OutlookMaryFinanceJuly
ExcelTomITAugust
MS WordHenryHRAugust

 

 

Desired Result
PeriodDepartmentdistinct employeesPercentage
MayFinance133%
MayHR133%
MayIT125%
JuneIT250%
JulyHR150%
JulyIT375%
JulyFinance267%
AugustIT375%
AugustHR150%

 

Hope this helps, and thank you in advance.

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] )
                )
            )
        )
)

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 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors