Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Grouping Values by Ranges Retreived from a Different Table under Two Conditions

Dear Community,   I have the following two tables in my data model: Using a DAX-Expression, the column "Headcount Range" in the "Company List" table shall be filled with the "Range" value fr...
Need Help
  • PaulDBrown's avatar
    PaulDBrown
    3 years ago

    Thank you for the file. See if this works for you.

    A. As a calculated column

    Headcount Range =
VAR _Threshold =
    CALCULATE (
        MIN ( 'Headcount Ranges'[Upper Threshold] ),
        FILTER (
            'Headcount Ranges',
            'Headcount Ranges'[Upper Threshold] >= 'Company List'[Headcount]
        )
    ) // Calculates the minimum upper threshold value in the Headcount table which is greater or equal to the Company list[Headcount]
RETURN
    LOOKUPVALUE (
        'Headcount Ranges'[Range],
        'Headcount Ranges'[Upper Threshold], _Threshold
    )
// Retruns the range value on the Headcount Ranges table where the upper threshold is the same as the calculated _Threshold value

    B. As a measure

    Headcount Range (measure) =
VAR _Threshold =
    CALCULATE (
        MIN ( 'Headcount Ranges'[Upper Threshold] ),
        FILTER (
            'Headcount Ranges',
            'Headcount Ranges'[Upper Threshold] >= SUM ( 'Company List'[Headcount] )
        )
    )
RETURN
    LOOKUPVALUE (
        'Headcount Ranges'[Range],
        'Headcount Ranges'[Upper Threshold], _Threshold
    )

    Sample PBIX file attached

PaulDBrown's avatar
PaulDBrown
Community Champion
3 years ago

It would help if you shared some sample dummy data or a link to a dummy PBIX file

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Paul, 

     

    thanks for your quick reply. Please find the dummy file under this link.

     

    Best,

    Wolfi

    • PaulDBrown's avatar
      PaulDBrown
      Community Champion
      3 years ago

      Thank you for the file. See if this works for you.

      A. As a calculated column

      Headcount Range =
VAR _Threshold =
    CALCULATE (
        MIN ( 'Headcount Ranges'[Upper Threshold] ),
        FILTER (
            'Headcount Ranges',
            'Headcount Ranges'[Upper Threshold] >= 'Company List'[Headcount]
        )
    ) // Calculates the minimum upper threshold value in the Headcount table which is greater or equal to the Company list[Headcount]
RETURN
    LOOKUPVALUE (
        'Headcount Ranges'[Range],
        'Headcount Ranges'[Upper Threshold], _Threshold
    )
// Retruns the range value on the Headcount Ranges table where the upper threshold is the same as the calculated _Threshold value

      B. As a measure

      Headcount Range (measure) =
VAR _Threshold =
    CALCULATE (
        MIN ( 'Headcount Ranges'[Upper Threshold] ),
        FILTER (
            'Headcount Ranges',
            'Headcount Ranges'[Upper Threshold] >= SUM ( 'Company List'[Headcount] )
        )
    )
RETURN
    LOOKUPVALUE (
        'Headcount Ranges'[Range],
        'Headcount Ranges'[Upper Threshold], _Threshold
    )

      Sample PBIX file attached