Create a relationship using Specific or All values

[gomezc73]

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 each row has the group of balance sheet accounts to summarize.

  It is something like this:

Report_Name	codeMenu	Line#	Submenu	Row Description	Submenu	Row Description	Account Type	Account Sub	Cat3	Cat6
Main Expenses	A002	6	A0026	Other Expenses	A00261	Maintenance Uniforms	1010	1900	REC	*All
Main Expenses	A002	6	A0026	Other Expenses	A00262	Misc.	1010	1360	REC	OTH
Main Expenses	A002	6	A0026	Other Expenses	A00265	Misc.	1010	1990	REC	*All
Main Expenses	A002	6	A0026	Other Expenses	A00269	tablecloths	7307	1002	REC	MAR

  On the Other hand, I have another file with a balance of transactions to summarize by group of accounts

  It is something like this:

Object	Sub	Description	BU Cat03	BU Cat06	JAN	FEB
1010	1900	Uniforms	REC	MAR	100.00	743.34
1010	1900	Uniforms	MOP	MAR	95.00	-
1010	1900	Uniforms	REC	SEA	302.00	488.00
1010	1900	Uniforms	MOP	MAR	35.00	65.00
1010	1990	Miscellaneous	REC	MAR	122.00	60.00
1010	1990	Miscellaneous	MOP	MAR	85.00	120.00
1010	1990	Miscellaneous	REC	SEA	100.00	101.49
1010	1990	Miscellaneous	MOP	MAR	505.00	-
1010	1990	Miscellaneous	REC	SEA	12.00	365.00
1010	1990	Miscellaneous	H1H	SEA	120.00	11.00
1010	1990	Miscellaneous	REC	MAR	878.00	-
7307	1002	Uniforms - Qualified	REC	OTH	-	250.00
7307	1002	Uniforms - Qualified	PHO	HTL	200.00	100.00
7307	1002	Uniforms - Qualified	REC	MAR	345.00	500.00
7307	1002	Uniforms - Qualified	FOO	HTL	122.00	55.00
7307	1002	Uniforms - Qualified	REC	SEA	250.00	300.00

   Now, in the report the idea is display 1 row with the total, in this Case 

Main Expenses	Expenses type	JAN	FEB
	Other Expenses	2,691.00	2,888.83

 Now, the problem I have is Summarize the amounts depending of the category codes (Cat3 and Cat 6), because sometimes I have specific Cat3 and Cat 6, by example the code A00269 have Cat3='REC' and Cat6='MAR', but in the other cases I have a specific Cat 3, but with all Category code 6 (By example the code A00261 has Cat3='REC' but with all category code 6 (Cat6).

 I can't create a relationship between both files, due there are a lot of records with distinct Category code 6 and combinations

If any of you have an idea of ​​how to handle this type of case, could you guide me on how to resolve it?

[MFelix · Accepted solution]

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.

[MFelix]

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?

[gomezc73]

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.

[MFelix]

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?

[gomezc73]

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? 

[MFelix]

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?

[gomezc73]

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,

[MFelix]

Hi @gomezc73 ,

I think now I understand your need however I'm confused about the final outcome you need.

Has you see below if I pickup the  January I get 2109 for the REC on CAT3 ifI compare this with the value you have (2.691) I'm missing 532. That I believe is the value of CAT6.

Can you please tell how you calculate dthe value in the example you gave

[gomezc73]

Hi, Sorry I have an error in my calculations.

I did a new table with both Files, the total for JAN must be 1859.

Table1 Report_Name	Table1 Row Description	Table1 Row Description	Table1 Account Type	Table1 Account Sub	Table1 Cat3	Table1 Cat6	Table2 Object	Table2 Sub	Table2 Cat03	Table2 Cat06	JAN	FEB
Main Expenses	Other Expenses	Maintenance Uniforms	1010	1900	REC	*All	1010	1900	REC	MAR	100.00	743.00
							1010	1900	REC	SEA	302.00	488.00
Main Expenses	Other Expenses	Misc.	1010	1360	REC	OTH
Main Expenses	Other Expenses	Misc.	1010	1990	REC	*All	1010	1990	REC	MAR	122.00	60.00
							1010	1990	REC	SEA	100.00	101.00
							1010	1990	REC	SEA	12.00	365.00
							1010	1990	REC	MAR	878.00	-
Main Expenses	Other Expenses	tablecloths	7307	1002	REC	MAR	7307	1002	REC	MAR	345.00	500.00
										Main Expenses	1,859.00	2,257.00

[MFelix · Accepted solution]

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.

[gomezc73 · Accepted solution]

That command CrossJoin works fine. I really appreciate your help.. thank you very much!!