Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
5 years ago

Segmentation with DAX

Good morning, everyone.

I'm creating a dashboard, which requires me to segment by Budget and Revenue, when budget is less than 100% and Revenue less than or equal to zero. For this, I have created an internal table with the word Budget and Revenue

amgalvisr_0-1625148106437.png

and I need to filter a grid with only the data that meets the condition when selecting either of the two.

I tried with a simple measure:

KPI Fail Measure = IF('KPI'[KPI selected]= "Budget", CALCULATETABLE(PROFIT, FILTER(PROFIT,PROFIT[Column1.BudgetCompl]<0)), CALCULATETABLE(PROFIT, FILTER(PROFIT,PROFIT[Column1.Revenue]<= 0))) however, I get an error that says that multiple columns cannot be converted to a scalar value
If anyone can help me, I would be very grateful. Happy day.

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Syndicate_Admin ,

     

    KPI Fail Measure = IF(selectedvalue('KPI'[KPI selected])= "Budget", CALCULATETABLE(PROFIT, FILTER(PROFIT,PROFIT[Column1.BudgetCompl]<0)), CALCULATETABLE(PROFIT, FILTER(PROFIT,PROFIT[Column1.Revenue]<= 0)))

     

    or

     

    KPI Fail Measure = IF(max('KPI'[KPI selected])= "Budget", CALCULATETABLE(PROFIT, FILTER(PROFIT,PROFIT[Column1.BudgetCompl]<0)), CALCULATETABLE(PROFIT, FILTER(PROFIT,PROFIT[Column1.Revenue]<= 0)))

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi  Syndicate_Admin ,

    You can modify Dax to the following:

    KPI Fail Measure =
IF (
    'KPI'[KPI selected] = "Budget",
    SUMX (
        CALCULATETABLE ( ' PROFIT ', ' PROFIT '[Column1.BudgetCompl] <= 0 ),
        [Column to be calculated]
    ),
    SUMX (
        CALCULATETABLE ( ' PROFIT ', ' PROFIT '[Column1.Revenue] <= 0 ),
        [Column to be calculated]
    )
)

    If the result does not meet your expectations, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

     

    Best Regards,

    Liu Yang

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