Forum Discussion

Dan_at_TWE's avatar
Dan_at_TWE
Icon for Helper III rankHelper III
7 months ago
Solved

Some help with loops, please. and an additional question

I have a master data set in a table in Power BI. Two of the relevent fields in the master dataset are 'Name' and 'Contract'. From this master dataset, I have contructed two other tables using the DISTINCT function. A Name table of unique names,  and a Contract table of unique contract names, from the dataset.

Name
Bob Smith
Sally Strumpet
Joe Blow

And...

Contract
Contract 1
Contract 2
Contract 3

 

Rather than using an additional data file, I would like to [partially] contruct a 'Billing Rates' Table that for each Contract, loops thru all the names to create entries in the Billing Rates table.  (For each contract, loop thru all the names.) Conceptually looking like this:

ContractName
Contract 1Bob Smith
Contract 1Sally Strumpet
Contract 1Joe Blow
Contract 2Bob Smith
Contract 2Sally Strumpet
Contract 2Joe Blow
Contract 3Bob Smith
Contract 3Sally Strumpet
Contract 3Joe Blow

 

I am unpracticed in [nested] loops in DAX to accomplish this. I was unable to modify code from the other examples, so far. 

 

I also have a question:

Once I am able (with your help, hopefully) to use looping to construct the first two columns the Rates Table above,  will power BI allow me to add a third column for manual entry of the 'billing rate' for each row of the billing rate table? Or are there no manual columns allowed in a table that is partially generated? If I wouldn't be able to manually enter the billing rates in a final column, trying not to use an additional external file would be pointless.

 

My Goal for the final table is this, with me entering the rates manually in the third column, of course.

ContractNameBilling Rate
Contract 1Bob Smith 
Contract 1Sally Strumpet 
Contract 1Joe Blow 
Contract 2Bob Smith 
Contract 2Sally Strumpet 
Contract 2Joe Blow 
Contract 3Bob Smith 
Contract 3Sally Strumpet 
Contract 3Joe Blow 

 

 

 

 

 

Need Help
Tips and Tricks
  • pcoley's avatar
    pcoley
    7 months ago

    Another (longer) way:

    Step 1: Create the Billing Rates Table with All Combinations

    Assuming your master table is called MasterData, and it contains the columns Name and Contract, you've already created:

    • A table called Names with unique names:
      dax
       
      Names = DISTINCT(MasterData[Name])
       
    • A table called Contracts with unique contracts:
      Contracts = DISTINCT(MasterData[Contract])
      To create the Billing Rates table that contains every possible combination of Contract + Name (i.e. a cross join / Cartesian product), use this DAX expression for a new table:
    Billing Rates = 
SELECTCOLUMNS(
    CROSSJOIN(
        Contracts,
        Names
    ),
    "Contract", [Contract],
    "Name",     [Name]
)
     
     

    Or, if you prefer to write it more explicitly using VALUES (especially useful if your dimension tables already use VALUES or DISTINCT):

     
    Billing Rates = 
SELECTCOLUMNS(
    CROSSJOIN(
        VALUES(MasterData[Contract]),
        VALUES(MasterData[Name])
    ),
    "Contract", [Contract],
    "Name",     [Name]
)
    Both versions produce the exact table structure you showed:
    • Contract 1 – Bob Smith
    • Contract 1 – Sally Strumpet
    • Contract 1 – Joe Blow
    • Contract 2 – Bob Smith

    ...and so on for all combinations.

    and the second question: for sum of BILLS per person per contract (most likely what you mean):

     
    Billing Rates = 
ADDCOLUMNS(
    CROSSJOIN(
        VALUES(MasterData[Contract]),
        VALUES(MasterData[Name])
    ),
    "Contract", [Contract],
    "Name",     [Name],
    "Billing Rate",
        CALCULATE(
            SUM(MasterData[BILLS]),
            MasterData[Contract] = EARLIER([Contract]),
            MasterData[Name]     = EARLIER([Name])
        )
)

    I hope this helps, if so please mark it as a solution. Kudos are welcome!
     

4 Replies

  • pcoley's avatar
    pcoley
    Icon for Super User rankSuper User
    7 months ago

    Dan_at_TWE Please try creating the table as follow; Changing the names of Table and fields as needed:

    Summary Table =
SUMMARIZE (
    MasterData,
    MasterData[Contract],
    MasterData[Name],
    "Billing Rate", SUM ( MasterData[Bills] )
)


    I hope this helps, if so please mark it as a solution; Kudos are welcome!

     

    • pcoley's avatar
      pcoley
      Icon for Super User rankSuper User
      7 months ago

      Another (longer) way:

      Step 1: Create the Billing Rates Table with All Combinations

      Assuming your master table is called MasterData, and it contains the columns Name and Contract, you've already created:

      • A table called Names with unique names:
        dax
         
        Names = DISTINCT(MasterData[Name])
         
      • A table called Contracts with unique contracts:
        Contracts = DISTINCT(MasterData[Contract])
        To create the Billing Rates table that contains every possible combination of Contract + Name (i.e. a cross join / Cartesian product), use this DAX expression for a new table:
      Billing Rates = 
SELECTCOLUMNS(
    CROSSJOIN(
        Contracts,
        Names
    ),
    "Contract", [Contract],
    "Name",     [Name]
)
       
       

      Or, if you prefer to write it more explicitly using VALUES (especially useful if your dimension tables already use VALUES or DISTINCT):

       
      Billing Rates = 
SELECTCOLUMNS(
    CROSSJOIN(
        VALUES(MasterData[Contract]),
        VALUES(MasterData[Name])
    ),
    "Contract", [Contract],
    "Name",     [Name]
)
      Both versions produce the exact table structure you showed:
      • Contract 1 – Bob Smith
      • Contract 1 – Sally Strumpet
      • Contract 1 – Joe Blow
      • Contract 2 – Bob Smith

      ...and so on for all combinations.

      and the second question: for sum of BILLS per person per contract (most likely what you mean):

       
      Billing Rates = 
ADDCOLUMNS(
    CROSSJOIN(
        VALUES(MasterData[Contract]),
        VALUES(MasterData[Name])
    ),
    "Contract", [Contract],
    "Name",     [Name],
    "Billing Rate",
        CALCULATE(
            SUM(MasterData[BILLS]),
            MasterData[Contract] = EARLIER([Contract]),
            MasterData[Name]     = EARLIER([Name])
        )
)

      I hope this helps, if so please mark it as a solution. Kudos are welcome!
       
      • pcoley's avatar
        pcoley
        Icon for Super User rankSuper User
        7 months ago

        And finally, using Power Query:

        • In Power Query Editor → select your MasterData query (or reference it if you want to keep the original unchanged).
        • Home tab → Group By.
        • In the Group By dialog:
          • Basic mode is fine if you only group by two columns.
          • Group by: Contract and Name (click "Advanced" to add the second column if needed).
          • New column name: e.g. Total Bills (or Billing Rate, Sum of Bills, etc.).
          • Operation: Sum.
          • Column: Bills.
        • Click OK.