Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Creating a new table based on multiple criteria evaluations with DAX

I have a data table and would like to create a new table based on multiple criteria (if it's possible to do this by grouping or modifying the existing table that's fine as well). In the new table I want to summarize by the Office Column and Subcategory column and have a third column that either has 'Yes', 'No', or 'N/A' in it. If the Attribute column is First and the Percentage is 0, then the new 3rd column  should be 'N/A', otherwise for each Subcategory I need to have the Percentage amount for the row with the Second value in the Attribute column subtract the Percentage amount for the row with the First value in the Attribute column and the resulting value is greater than or equal to 0, the new third column in the new table should say 'Yes', otherwise 'No'.  Below is a sample of the data with a fourth (unneccessary) explanation column to help show the desired result.

 

OfficeSubcategoryAttributePercentageDesired Result w/Explanation
1ABFirst0%'N/A' - Percentage for First is 0%
1ABSecond0%
1ABCFirst10%'Yes' - Percentage for Second - Percentage for First is 0
1ABCSecond10%
1ABCDFirst10%'No' - Percentage for Second - Percentage for First is <0
1ABCDSecond5%
2ABFirst10%'Yes' - 20%-10%>=0
2ABSecond20%
2ABCFirst0%'N/A' - 0% for First
2ABCSecond-10%
2ABCDFirst10%'No' - 0%-10%<0
2ABCDSecond0%

 

Need Help
Tips and Tricks
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    Hi, Anonymous 

     

    You can try the following methods.

    Column =
VAR _first =
    CALCULATE (
        MIN ( 'Table'[Percentage] ),
        FILTER (
            'Table',
            [Subcategory] = EARLIER ( 'Table'[Subcategory] )
                && [Attribute] = "First"
                && [Office] = EARLIER ( 'Table'[Office] )
        )
    )
VAR _second =
    CALCULATE (
        MIN ( 'Table'[Percentage] ),
        FILTER (
            'Table',
            [Subcategory] = EARLIER ( 'Table'[Subcategory] )
                && [Attribute] = "Second"
                && [Office] = EARLIER ( 'Table'[Office] )
        )
    )
RETURN
    IF ( _first = 0, "N/A", IF ( _second - _first >= 0, "Yes", "No" ) )

    Table:

    New table =
SUMMARIZE ( 'Table', 'Table'[Office], 'Table'[Subcategory], 'Table'[Column] )

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    I realize that including the desired result can be a bit confusing as it doesn't show the actual layout I'm seeking - this would be the actual final output:

     

    OfficeSubcategoryCol 3
    1ABNA
    1ABCYes
    1ABCDNo
    2ABYes
    2ABCN/A
    2ABCDNo
  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, Anonymous 

     

    You can try the following methods.

    Column =
VAR _first =
    CALCULATE (
        MIN ( 'Table'[Percentage] ),
        FILTER (
            'Table',
            [Subcategory] = EARLIER ( 'Table'[Subcategory] )
                && [Attribute] = "First"
                && [Office] = EARLIER ( 'Table'[Office] )
        )
    )
VAR _second =
    CALCULATE (
        MIN ( 'Table'[Percentage] ),
        FILTER (
            'Table',
            [Subcategory] = EARLIER ( 'Table'[Subcategory] )
                && [Attribute] = "Second"
                && [Office] = EARLIER ( 'Table'[Office] )
        )
    )
RETURN
    IF ( _first = 0, "N/A", IF ( _second - _first >= 0, "Yes", "No" ) )

    Table:

    New table =
SUMMARIZE ( 'Table', 'Table'[Office], 'Table'[Subcategory], 'Table'[Column] )

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

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