Forum Discussion

gomezc73's avatar
gomezc73
Helper V
4 years ago
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...
Need Help
  • MFelix's avatar
    MFelix
    4 years ago

    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's avatar
    gomezc73
    4 years ago

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

MFelix's avatar
MFelix
Super User
4 years ago

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's avatar
    gomezc73
    Helper V
    4 years ago

    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's avatar
      MFelix
      Super User
      4 years ago

      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's avatar
        gomezc73
        Helper V
        4 years ago

        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?