Forum Discussion
Create a relationship using Specific or All values
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.
Hi gomezc73 ,
If you create the dimension tables for CAT3 and CAT6 with unique values then those dimensions will filter out everything. you will simulate a many to many with a many to one to many.
Or your question is how to write the first table without a single line for each value?
Hi, In my Report i need display only 1 line with the summary by Account OBJ, Account Sub, Cat3 and Cat 6..
I didn't understand your solution, do yo mean, create a table for Categories 3(Cat3), other table for Categories 6 (Cat 6)?.. ?.. how I can relate each tabla with my current tables?
Hi gomezc73 ,
When I refer a relationship is a model similar to this one:
Has you can see there is a relationship between both tables done by the dimension tables.
My question now refers to CAT6 since you have on the first table a value of ALL will you have a line for eaxch one, so if you do a model similar to the one above it will work properly or will you have the value ALL in the CAT 6 and you want to pick up all the CAT on the transactions even if they are not explicit in the Group balance?
Hi Miguel,
Using your image 'Group Balance" When the field Cat6='ALL', I don't have in the file of details records with CAT6='ALL', because that value is not a valid CAT6 (It is only a reference value to identify that in the second table can exist multiples CAT6 that must be summarize)
By example, in the file of details can exist records with CAT6 with these values ("MAR","COE","REP","RWE","PEE", etc) (Exist around 30 valid CAT6).
Then, The idea is:
A) If I found a record in the First table with CAT6=ALL, then I need summarize the records in the Second table with CAT6 IN ("MAR","COE","REP","RWE","PEE", etc).
B) For any other value in the first table Distint to ALL, i can look the exact value in the second file, By Example: If I found a record in the First table with CAT6="MAR", then I need summarize only records in the Second table with CAT6="MAR".
I hope this help to understand my problem,