Counting and searching rows with common values?

[vpsoini]

I seem to bump up regurarly with requirements, where I need to search and/or count some values from rows, that share a common values in some column. 

I have tried to learn how to use CALCULATE / COUNTROWS / CONTAINSTRING , but combining all those still puzzles me.  Below is a small table where some rows share a common value (here ordernumber) :

Table "orders"

Ordernumber	Item	Description	Serial number	Quantity	Date
001	A1234	Machine 110	00123	1	10.10.2021
001	O1234	Option 1234		1	10.10.2021
001	O2345	Option 2345		2	10.10.2021
002	A2345	Machine 200	00234	1	13.10.2021
002	O2345	Option 2345		1	13.10.2021
003	A1234	Machine 110	00124	1	15.10.2021
003	O2345	Option 2345		2	15.10.2021
003	O3456	Option 3456		2	15.10.2021
003	O4567	Option 4567		1	15.10.2021
003	O5678	Option 5678		1	15.10.2021
004	A3456	Machine 250	00256	1	15.10.2021
004	O1234	Option 1234		1	15.10.2021
004	O2345	Option 2345		2	15.10.2021
005	A2345	Machine 200	00235	1	19.10.2021
005	O2345	Option 2345		1	19.10.2021
006	A1234	Machine 110	00125	1	21.10.2021
006	O4567	Option 4567		2	21.10.2021

Could you share the correct format for following cases

• How many orders have "Machine 110" with "Option 4567" ?   (answer = 2 , orders 003 and 006)
• How many "Option 2345" was ordered with 200-series machines (200/250) between 10.-15.10.2021 ? (answer = 3 , 1 in order 002 and 2 in order 004. Order 005 is out of date range) , If you could provide this sample with using CONTAINSTRING to find strings "200" and "250" , instead of exact full machine names...

And bit more complicated case: If we have a related table e.g. customer info linked with serial number:

Table "Customers"

Name	Address	Country	Serial number	Order paid
Customer 1	Street 1 , City 1	FI	00123	OK
Customer 2	Street 2, Cisty 2	SE	00234
Customer 3	Street 3, City 3	UK	00124	OK
Customer 4	Street 4, City 4	FI	00256	OK
Customer 5	Street 5, City 5	DE	00235
Customr 6	Street 6, City 6	DE	00125	OK

Sample question could be:

• How many Option 1234 has bee sold to FI ?  (answer = 2, one in order 001 and another in order 004)

Those samples would give me enough variants to play with and adapt to.  Some text around solution, why it was made would also be much appreciated (for learning purposes)

Thanks a 10^6 in advance 🙂

ps. sorry, but I cannot seem to get tables to maintain their correct look, but this always puts them in funny format...

[Greg_Deckler]

@vpsoini Third one:

Measure =
  VAR __Table = ADDCOLUMNS('Table',"__Country",RELATED('Table2'[Country])
  VAR __FI = DISTINCT(SELECTCOLUMNS(FILTER(__Table,[Country]="FI"),"__ON",[Ordernumber]))
  VAR __Option1234 = DISTINCT(SELECTCOLUMNS(FILTER(__Table,[Description]="Option 1234"),"__ON",[Ordernumber]))
RETURN
  COUNTROWS(INTERSECT(__FI,__Option1234))

[Greg_Deckler]

@vpsoini Second one:

Measure 9 = 
    VAR __Machine2 = DISTINCT(SELECTCOLUMNS(FILTER('Table6',[Date]>=DATE(2021,10,10) && [Date]<=DATE(2021,10,15) && (CONTAINSSTRING([Description],"Machine 200") || CONTAINSSTRING([Description],"Machine 250"),"__ON",[Ordernumber ]))
    VAR __Option2345 = DISTINCT(SELECTCOLUMNS(FILTER('Table6',[Date]>=DATE(2021,10,10) && [Date]<=DATE(2021,10,15) && [Description]="Option 2345"),"__ON",[Ordernumber ]))
RETURN
    COUNTROWS(INTERSECT(__Machine2,__Option2345))

[Greg_Deckler]

@vpsoini For the first one, try:

Measure 9 = 
    VAR __Machine110 = DISTINCT(SELECTCOLUMNS(FILTER('Table6',[Description]="Machine 110"),"__ON",[Ordernumber ]))
    VAR __Option4567 = DISTINCT(SELECTCOLUMNS(FILTER('Table6',[Description]="Option 4567"),"__ON",[Ordernumber ]))
RETURN
    COUNTROWS(INTERSECT(__Machine110,__Option4567))

[vpsoini]

Hi @Greg_Deckler 

Thanks for the suggestion. However, when I test that based on serial number and option (where I should get "1" as result) , the above solution gives me "2"  Does the COUNTROWS count rows with both matches (line with certain serial number + the option line attached to that machine (serial)?  So should I have " -1" in order to get the option only?

What does that "__ON" do in those variables?

Thanks again 🙂

[v-kelly-msft · Accepted solution]

Hi @vpsoini ，

For the first one,create 4 measures as below:

Machine 110" with "Option 4567 =
VAR _table =
    CALCULATETABLE (
        VALUES ( 'Table'[Description] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Ordernumber] = MAX ( 'Table'[Ordernumber] ) )
    )
RETURN
    IF ( "Machine 110" IN _table && "Option 4567" IN _table, 1, 0 )

Measure =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Ordernumber] ),
    FILTER ( VALUES ( 'Table'[Ordernumber] ), [Machine 110" with "Option 4567] = 1 )
)

Option 2345 with 200-series machines (200/250) =
VAR _table =
    CALCULATETABLE (
        VALUES ( 'Table'[Description] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Ordernumber] = MAX ( 'Table'[Ordernumber] ) )
    )
RETURN
    IF (
        ( "Machine 200"
            IN _table
            || "Machine 250" IN _table )
            && "Option 2345"
                IN _table
                    && (
                        MAX ( 'Table'[Date] ) >= DATE ( 2021, 10, 10 )
                            && MAX ( 'Table'[Date] ) <= DATE ( 2021, 10, 15 )
                    ),
        1,
        0
    )

Measure2 =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Ordernumber] ),
    FILTER (
        VALUES ( 'Table'[Ordernumber] ),
        'Table'[Option 2345 with 200-series machines (200/250)] = 1
    )
)

And you will see:

For the second one:

Create 2 measures as below:

Option 1234 has bee sold to FI =
VAR _Series =
    CALCULATETABLE (
        VALUES ( 'Table (2)'[Serial number] ),
        FILTER ( ALL ( 'Table (2)' ), 'Table (2)'[Country] = "FI" )
    )
VAR _Numbers =
    CALCULATETABLE (
        VALUES ( 'Table'[Ordernumber] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Serial number] IN _Series )
    )
VAR _Description =
    CALCULATETABLE (
        VALUES ( 'Table'[Description] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Ordernumber] = MAX ( 'Table'[Ordernumber] )
                && 'Table'[Ordernumber] IN _Numbers
        )
    )
RETURN
    IF ( "Option 1234" IN _Description, 1, 0 )

Measure3 =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Ordernumber] ),
    FILTER (
        VALUES ( 'Table'[Ordernumber] ),
        'Table'[Option 1234 has bee sold to FI] = 1
    )
)

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[Greg_Deckler]

@vpsoini 

• "How many orders have "Machine 110" with "Option 4567" ?   (answer = 2 , orders 003 and 006)"

"__ON" is simply the name of the column in the single column table created by SELECTCOLUMNS, it can be pretty much anything as long as both are the same.