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 gomezc73 ,
In this case you should create two dimension tables with the Cat3 and CAT 6 then relate this tables with the other two that will create the relationship that you refer.
Just one question when it says on CAT6: *All is this the value that you have or you did not place all of the values on the example because they are a lot?
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?