Forum Discussion

SarWal's avatar
SarWal
Microsoft Employee
9 years ago
Solved

How to compare values from another table? Segmentation in Dax

Hello - PBI Newbie here -

 

I am trying to segment accounts based on revenue. I have a table with Account information, Financial information and Segmentation requirements.

 

Account Columns:

[Account_Id] [Account Name] etc

Financial Columns

[Account_Id] [Revenue] etc

 

Segmentation Columns:

[Segmentation Name] [Max Revenue] [Min Revenue]

 

What is the best approach to solve this?

I want a table/chart with the Account Name, Revenue, and Segmentaion Name, or to be able to filter a page based off the segmentation. I am struggling with the format of the column I'm adding to the Financial table in order to calculate what segment each account is in. NOTE there is only one line item for each account in the financials table.

 

Thanks in advance for your help!

 

 

Note - I've been looking at this page and trying to convert it to columns (rather than measures), and not having any luck.

  • Eric_Zhang's avatar
    Eric_Zhang
    9 years ago

    SarWal

     

    You can try to add a calculated column as below.

    Segmentation Name = 
CALCULATE (
    VALUES ( Segmentation[Segmentation Name] ),
    FILTER (
        Segmentation,
        Segmentation[Min Revenue] <= Financial[Revenue]
            && Segmentation[Max Revenue] >= Financial[Revenue]
    )
)

     

1 Reply

  • Eric_Zhang's avatar
    Eric_Zhang
    Microsoft Employee
    9 years ago

    SarWal

     

    You can try to add a calculated column as below.

    Segmentation Name = 
CALCULATE (
    VALUES ( Segmentation[Segmentation Name] ),
    FILTER (
        Segmentation,
        Segmentation[Min Revenue] <= Financial[Revenue]
            && Segmentation[Max Revenue] >= Financial[Revenue]
    )
)