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
Anonymous
Not applicable

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,

1 ACCEPTED SOLUTION

Hi @Anonymous ,

 

We can create a table contain all the range and the order number of ranges

 

11.PNG

 

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

12.PNG

 

The solution for order the x-axis is to put the index column into the tooltips.

 

13.PNG

 

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.

 

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.

View solution in original post

7 REPLIES 7
MFelix
Super User
Super User

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Thank you @MFelix  This will be very useful

Anonymous
Not applicable

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 IDCompa Ratio Range before IncreaseCompa Ratio Range after Increase
A123Below 70%70-79%
B25690-99%90-99%
C75880-89%90-99%
D547100-110%Above 110%
E78980-89%90-99%
F45870-79%80-89%
G877100-110%100-110%
H55890-90%100-110%
G54880-89%90-99%
L825Below 70%Below 70%

Current DisplayCurrent DisplayWhat we're looking forWhat we're looking for

 

Thanks!

 

 

Hi @Anonymous ,

 

We can create a table contain all the range and the order number of ranges

 

11.PNG

 

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

12.PNG

 

The solution for order the x-axis is to put the index column into the tooltips.

 

13.PNG

 

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.

 

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.
Anonymous
Not applicable

Thank you @v-lid-msft  this did the trick 😄

Hi @Anonymous ,

 

The solution given by @v-lid-msft , should work as expected.

 

@v-lid-msft  just one thing there is no need to add the index on the visual if you select the column Range on the table that is created for the Ranges then select the Sort by and choose the column index the Range will be sorted without the need to be placed in the visuals.

sort.png

Regards,

MFelix

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi @MFelix ,

 

You are right, thank you for your addition information, I have learned a lot from there.

 

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.

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.

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.