DAX - Measure count active products

0
votes

Tried a lot but didn't get any expected result.

I need a DAX measure which shows me the second result:

Based on my 2 tables, I need to know how many products is active if they appear in the second table - then 1, else 0

Product1
A
B
C
D
E
F


Product2
A
J
K
A
B
J
F
F
F

The result should be:

Product Measure
A   1
B   1
C   0
D   0
E   0
F   1

Count: 3

1
ssaspowerbidaxpowerpivot

1 Answers

0
votes

Try the following:

  • In the data model view, make a relationship between Product1 table and Product2 table... this should load by default as a one to many relationship
  • Make a new table in Power BI or a pivot table in Excel. Put Products column from Products1 table on rows
  • Write a new measure as follows and drop it into your table

Measure Name = 
  IF(
    COUNTROWS(Product2) > 0, 1, 0
  )

Hopefully something like that works for you.

Jake