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 from the "Headcount Ranges" table, by applying the condtion that "Headcount" is greater or equal to "Lower Threshold" AND smaller or equal to "Upper Threshold".

 

Besides RELATED(), I have been experimenting with the LOOKUPVALUE() function, but there does not seem to be a way to formaulate the condition in the third argument, is there?.

Could sombedoy help me out with a solution, please?

 

Best regards,

Wolfi

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

4 Replies

  • 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

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Many thanks, Paul, for a job very well done! Your solution works just as it is supposed to!