Forum Discussion

RichOB's avatar
RichOB
Icon for Post Partisan rankPost Partisan
1 year ago
Solved

Table with every combination

Hi, using the table1, how do I get every combination like in table2 please?

 

Table1

SystemLocation
LaptopNewcastle
LaptopNewcastle
EmailNewcastle
ManchesterCRM
ManchesterCRM
ManchesterCRM


Table2

SystemLocationCount
LaptopNewcastle2
EmailNewcastle1
CRMNewcastle0
LaptopManchester0
EmailManchester0
CRMManchester3


Thanks

Need Help
  • freginier's avatar
    freginier
    1 year ago

    Hey there!

     

    You can achieve the result in Power BI / DAX or SQL by generating every possible combination of System and Location, and then counting the occurrences.

     

    Use the following DAX formula to create a new table in Power BI:

    Table2 =
    VAR Systems = VALUES(Table1[System])
    VAR Locations = VALUES(Table1[Location])

    RETURN
    ADDCOLUMNS(
    CROSSJOIN(Systems, Locations),
    "Count", COUNTROWS(FILTER(Table1, Table1[System] = EARLIER([System]) && Table1[Location] = EARLIER([Location])))
    )

    What this does:

    VALUES(Table1[System]) → Gets unique systems
    VALUES(Table1[Location]) → Gets unique locations
    CROSSJOIN() → Creates all possible combinations
    COUNTROWS() → Counts occurrences of each pair

     

    Hope this helps!

    😁😁

3 Replies

  • freginier's avatar
    freginier
    Icon for Solution Sage rankSolution Sage
    1 year ago

    Hey there!

     

    You can achieve the result in Power BI / DAX or SQL by generating every possible combination of System and Location, and then counting the occurrences.

     

    Use the following DAX formula to create a new table in Power BI:

    Table2 =
    VAR Systems = VALUES(Table1[System])
    VAR Locations = VALUES(Table1[Location])

    RETURN
    ADDCOLUMNS(
    CROSSJOIN(Systems, Locations),
    "Count", COUNTROWS(FILTER(Table1, Table1[System] = EARLIER([System]) && Table1[Location] = EARLIER([Location])))
    )

    What this does:

    VALUES(Table1[System]) → Gets unique systems
    VALUES(Table1[Location]) → Gets unique locations
    CROSSJOIN() → Creates all possible combinations
    COUNTROWS() → Counts occurrences of each pair

     

    Hope this helps!

    😁😁

  • rajendraongole1's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    1 year ago

    Hi RichOB  - You can achieve this in Power BI using a cross join to get all possible combinations of System and Location, followed by a COUNTROWS measure to count occurrences. 

     

    Table2 =
    CROSSJOIN(
        DISTINCT( Tab1[System] ),
        DISTINCT( Tab1[Location] )
    )
     

     

     

    Count =
    VAR sys = SELECTEDVALUE(Table2[System])
    VAR loc = SELECTEDVALUE(Table2[Location])

    VAR rowCount =
        CALCULATE(
            COUNTROWS(tab1),
            tab1[System] = sys,
            Tab1[Location] = loc
        )

    RETURN
    IF(ISBLANK(rowCount), 0, rowCount)
     
     
    Please find the attached pbix file . hope thishelps.

     

     

     

  • RichOB's avatar
    RichOB
    Icon for Post Partisan rankPost Partisan
    1 year ago

    Thanks for the solution and explanation!! 🙂