Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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.
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])))
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..
Tried it, same issue -:)
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.
That's it, great!
Thanks, appreciate yopur help!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |