ISBLANK Funtion on values and slicers

[popescu]

A table

- with 1 row containing an itemcode

- two different columns measures each containing project and item related related transactions:

PROJECT 1 (selected via the slicer "project") results in:

                Budget        Invoiced

ITEM A                        700

ITEM B   1000             1200  

ITEM C   800

Colums are measures:

- Budget = SUM('Budgets (SQL)'[Budget])  (the SQL query only retrieves items that have a budget value)

- Actuals = SUM('Invoiced (SQL)'[Invoiced])  (the SQL query only retrieves items that have been invoiced)

-> I want to show a zero instead of the blank values. 

when using IS BLANK (IF(ISBLANK(SUM('Budgets (SQL)'[Budget]));0;SUM('Budgets (SQL)'[Budget]) ,  this works out fine however also items that are in the database but for which there's no budget of which haven't been invoiced for the selected project, are displayed as well then:

PROJECT 1 (selected via slicer):

                Budget        Invoiced

ITEM A   0                     700

ITEM B   1000             1200  

ITEM C   800               0

ITEM D  0                    0

ITEM E  0                    0

-> when selecting a project via the slicer, I only want to display those items that have been budgetted and/or invoiced for that particular project, showing zero's if there's a blank value. 

(The three tables are related via the project id and item id (one direction, one-to-many) )

How can this be realized?

[tex628]

Budget = 
IF(
SUM('Budgets (SQL)'[Budget]) <= 0 &&
SUM('Invoiced (SQL)'[Invoiced]) <= 0 ; BLANK() ;
IF(SUM('Budgets (SQL)'[Budget]) <0 ; 0 ; SUM('Budgets (SQL)'[Budget])))

Invoiced = 
IF( 
SUM('Budgets (SQL)'[Budget]) <= 0 && 
SUM('Invoiced (SQL)'[Invoiced]) <= 0 ; BLANK() ; 
IF(SUM('Invoiced (SQL)'[Invoiced]) <0 ; 0 ; SUM('Invoiced (SQL)'[Invoiced])))

[popescu]

Thanks, but: the example I gave is a simplification of the real table which contains about 7 different columns (so budget; commiment, sales order, sales invoice, time, expenses...). Next to that there 's calculated columns as well..

Hoped that there would be an easier solution? I don't understand why my isblank function is causing the project slicer to behave differently. The relationships between the "tables/queries behind the several columns" are still in place..

[tex628]

Whats the result if you do a simple 
SUM(Budget) + 0

[popescu]

Tried it, same issue -:)

[tex628 · Accepted solution]

Then i would do a check measure: 

Measure = 
IF(
Dimension 1 = BLANK() &&
Dimension 2 = BLANK() &&
Dimension 3 = BLANK() &&
Dimension 4 = BLANK() &&
Dimension 5 = BLANK() &&
Dimension 6 = BLANK() &&
Dimension 7 = BLANK() ; 0 ; 1)

Then put a filter on this measure. If the sum of all 7 dimensions are blank you want the remove the row.

[popescu]

That's it, great!

Thanks, appreciate yopur help!

[tex628]

No worries! 🙂