Forum Discussion
Solved
Create a relationship using Specific or All values
Hi, I have an issue and I don't have idea how solve it.. I need help.. please.. I am creating a balance report in PBI, in a File I have the menu with the options (rows) to display and eac...
Hi gomezc73 ,
Try the following formula:
Total Values = VAR temp_table = FILTER ( CROSSJOIN ( 'Balance sheet accounts', 'Balance Transactions' ), 'Balance sheet accounts'[Account Sub] = 'Balance Transactions'[Sub] && 'Balance sheet accounts'[Account Type] = 'Balance Transactions'[Object] && 'Balance sheet accounts'[Cat3] = 'Balance Transactions'[BU Cat03] && ( 'Balance sheet accounts'[Cat6] = 'Balance Transactions'[BU Cat06] || 'Balance sheet accounts'[Cat6] = "*All" ) ) RETURN SUMX ( temp_table, 'Balance Transactions'[Value] )This is giving the correct result for the small sample you present however not sure if it will return correctly on your overall values since the details can make all the difference:
Also be aware that I have unpivot the transactions in order not to have a calculation for each column.
Check PBIX file attach.
That command CrossJoin works fine. I really appreciate your help.. thank you very much!!
Hi Miguel,
There are aprox 20 Category 6 (Cat6), then when I say '*All' is to no write in details the 20 codes of Cat6.
That's my concerns, in some rows I have to link both file only with 1 Cat 6, and there are other rows where no matter, because it is for all Cat6.