Segmentation with DAX

[Syndicate_Admin]

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

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.

[Anonymous]

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

[amitchandak]

@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)))

[Syndicate_Admin]

Thanks for trying, however the same error still came out.