Forum Discussion
Append 2 DAX tables
Hi everyone,
I have a large table that has, among many other columns, Worker ID, Compa Ratio Range before Increase, and Compa Ratio Range after Increase. We would like to compare number of workers in each range before and after increase, we tried doing a bar or bar and chart graphs but because the ranges are in different columns, we couldn't find an axis or a legend that worked for both.
I am mostly an R user so what I would do is create 2 new tables, one with Worker ID and Compa Ratio Range before Increase, add a column Type = "Before Increase", and a second table with the columns Worker ID and Compa Ratio Range after Increase, and add a new column Type = "After Increase". Finally, I would append the 2 tables and now I can use Type as my legend and display the 2 side by side.
I can't use Append Queries because my 2 tables are DAX tables, is there another formula I could use to append them or a chart that will allow me to display the 2 columns side by side?
Thank you,
Hi Anonymous ,
We can create a table contain all the range and the order number of ranges
Then we can create two measure to calculate the value.
After Increase =
CALCULATE (
COUNT ( 'Table'[Worker ID] ),
FILTER (
ALLSELECTED ( 'Table' ),
CONTAINS (
FILTERS ( RangeTable[Range] ),
[Range], [Compa Ratio Range after Increase]
)
)
)Before Increase =
CALCULATE (
COUNT ( 'Table'[Worker ID] ),
FILTER (
ALLSELECTED ( 'Table' ),
CONTAINS (
FILTERS ( RangeTable[Range] ),
[Range], [Compa Ratio Range before Increase]
)
)
)The solution for order the x-axis is to put the index column into the tooltips.
The range table does not need to has relation with other tables.
BTW, pbix as attached.
Best regards,
Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 Replies
Hi Anonymous ,
Looking at your description believe that this can be made using two measures or a column on the model you already have instead of creating one or two new tables.
Can you please share some sample data and expected result?
Regards,
MFelix
Hi MFelix
Below is what the table looks like and the 2 graphs we had side by side for count of workers in the different ranges before/after increase. You can see that even the scale is not the same, which is another issue. So we're looking for side-by-side bars instead. And the Axis needs to be ordered in ascending order (Below 70, 70-79, 80-89, etc).
I tried creating a measure to count the number of times each range appears (frequency), but even then we haven't been able to find what to use as an axis. We tried creating a new table with the ranges and order we needed them (using RELATED and all that), but we can't create a relationship between the 2 columns in my original table to one column in the new table.
Worker ID Compa Ratio Range before Increase Compa Ratio Range after Increase A123 Below 70% 70-79% B256 90-99% 90-99% C758 80-89% 90-99% D547 100-110% Above 110% E789 80-89% 90-99% F458 70-79% 80-89% G877 100-110% 100-110% H558 90-90% 100-110% G548 80-89% 90-99% L825 Below 70% Below 70% Current DisplayWhat we're looking for
Thanks!
Hi Anonymous ,
We can create a table contain all the range and the order number of ranges
Then we can create two measure to calculate the value.
After Increase =
CALCULATE (
COUNT ( 'Table'[Worker ID] ),
FILTER (
ALLSELECTED ( 'Table' ),
CONTAINS (
FILTERS ( RangeTable[Range] ),
[Range], [Compa Ratio Range after Increase]
)
)
)Before Increase =
CALCULATE (
COUNT ( 'Table'[Worker ID] ),
FILTER (
ALLSELECTED ( 'Table' ),
CONTAINS (
FILTERS ( RangeTable[Range] ),
[Range], [Compa Ratio Range before Increase]
)
)
)The solution for order the x-axis is to put the index column into the tooltips.
The range table does not need to has relation with other tables.
BTW, pbix as attached.
Best regards,
Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you MFelix This will be very useful
