CODEWITHSUNDEEP
powerbidax
SQLfun
SQLfun

Reputation: 53

Power BI (DAX) Distinct Count Filtered by Condition (Where Clause)

I have a customer table with customer transactions. Each customer transaction has Transaction ID and each product have Product Code and other fields of the customer is Region Code, Country Code. I can do this with SQL but I need to convert this to DAX.

Task 1 Count all Transactions where the Product code = '1'.

Task 2 Count all Transactions where the Product code = '2' and Region Code = '100'.

-Question 3

Task 3 Count all Transactions where the Product code = '2' and Region Code = '100' AND Contry Code = '001'.

Please note this will be measures shown as KPI/display card on the dashboard

Thanks so much for your time and help.

In SQL, it will look something like below

Task 1: 

SELECT COUNT(*) as CountC
WHERE  ProductCode = '1'

Task 2.

SELECT COUNT(*) as CountC
WHERE  ProductCode = '2'
AND RegionCode = '100'
Group by...

Upvotes: 1

Views: 2751

Answers (1)

Marco Vos
Marco Vos

Reputation: 2968

Formats you could use are:

[Measure] :=
CALCULATE (
    COUNTROWS ( MyTable ),
    'Mytable'[column1] = x,
    'Mytable'[column2] = y
)

or

[Measure] :=
    COUNTROWS (
    FILTER ( MyTable, 'Mytable'[column1] = x && 'Mytable'[column2] = y )
)

Upvotes: 2

Related Questions