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

1
votes

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.

  • Question 1

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

  • Question 2

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...
1
powerbidax

1 Answers

2
votes

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 )
)