Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 worker...
Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    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.

     

v-lid-msft's avatar
v-lid-msft
Community Support
6 years ago

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.

 

MFelix's avatar
MFelix
Super User
6 years ago

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.

Regards,

MFelix

 

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    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.