Measure question

[MadisMerk]

Can you help me with a measure? Let us imagine I have this table as a starting point

Vehicle number	Customer name	Vehicle type	Amount
550 RLA	Toomas	New	1
885 ASS	Toomas	Used	1
854 PPT	Toomas	New	1
754 TYU	Anne	Used	1
125 ASK	Anne	Used	1
855 ANN	Juss	New	1
999 SVE	Juss	Used	1

I would like to now only show the customers and the amounts for customers that have only used vehicles and no new ones. So the end result would look like this:

Customer name	Amount
Anne	2

[Zubair_Muhammad · Accepted solution]

@MadisMerk 

Try this MEASURE

Measure =
IF (
    COUNTROWS ( VALUES ( TableName[Vehicle type] ) ) = 1
        && CALCULATE (
            DISTINCTCOUNT ( TableName[Vehicle type] ),
            TableName[Vehicle type] = "Used"
        ) = 1,
    COUNT ( TableName[Vehicle type] )
)

[MattAllington]

It is not easy to do this with 1 table. You should load a customer dimension table too, containing all unique customers. Join this to your data table with a 1 to many relationship. Then put the customer from the customer table into the rows of a matrix. The measures would be

Total new = calculate(sum(datatable[amount]),datatable[vehicle type]=“new”)

Total but only customers with no new vehicles =calculate(sum(datatable[amount]),filter(customertable,[total new]=0))

i haven’t tested this, but it think it is correct.