Table with every combination

[RichOB]

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

Table1

System	Location
Laptop	Newcastle
Laptop	Newcastle
Email	Newcastle
Manchester	CRM
Manchester	CRM
Manchester	CRM

Table2

System	Location	Count
Laptop	Newcastle	2
Email	Newcastle	1
CRM	Newcastle	0
Laptop	Manchester	0
Email	Manchester	0
CRM	Manchester	3

Thanks

[RichOB]

Thanks for the solution and explanation!! 🙂

[rajendraongole1]

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.

[freginier · Accepted solution]

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!

😁😁