Forum Discussion

EvertonRamone's avatar
EvertonRamone
Helper I
8 years ago

DAX - Measure count active products

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:

 

ProductMeasure
A1
B1
C0
D0
E0
F1

 

Count: 3

 

 

 

Need Help

3 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    EvertonRamone

     

    Try this MEASURE

    Measure =
IF (
    HASONEFILTER ( Table1[Product1] ),
    IF ( SELECTEDVALUE ( Table1[Product1] ) IN VALUES ( Table2[Product2] ), 1, 0 ),
    SUMX (
        VALUES ( Table1[Product1] ),
        CALCULATE (
            IF ( SELECTEDVALUE ( Table1[Product1] ) IN VALUES ( Table2[Product2] ), 1, 0 )
        )
    )
)

     

    See file attached as well

     

    • EvertonRamone's avatar
      EvertonRamone
      Helper I
      8 years ago

      Zubair_Muhammad,
      Really nice. But I couldn't make it in my model:

       

      Look, I have these tables:

      FactSales   
      KeyDateKeyCustomerKeyProductTotal
      11112,9
      12213
      131156,4
      141564,8
      211894,8
      22156,5
      312564,85
      323564,8
      4111325,6
      421132,3

       

      Customer
      KeyCustomerName
      1Jean
      2Mari
      3Lisa
      4Julian
      5Jhonny

       

      Calendar
      KeyDateDate
      101/01/2018
      202/01/2018
      301/05/2018
      401/08/2018

       

      Product
      KeyProductProduct
      1A
      2B
      3C

       

      Rule:

      1. Make a list of all distinct customers whose someday went to [IdEspeciality] = 2 and the date of it is 90 days less than my actual filter date

      2. I've created a variable that keeps me my last 90 days so I can compare to the first one and see how many customer has appeared 

      3. If customer was in the first list ( [IdEspeciality] = 2 ) and in my last 90 days he is appearing also in  [IdEspeciality] = 2 , then 1, else 0

       

      I tried something like this

       

      Actime Customers test =

       

      Teste Ativos 2 = 

VAR Anterior3Meses = MAX(DimDate[Date]) - 90

RETURN 
CALCULATE(
    DISTINCTCOUNT(FactAnswers[Dpaci_sq]);
    FILTER( ALL (DimDate[Date] );
         DimDate[Date] < Anterior3Meses);
         DimEspeciality[IdEspeciality] = 2;         
    FILTER(FactAnswers; CONTAINS(
          FILTER(FactAnswers; 
          RELATED(DimEspeciality[IdEspeciality]) = 2 &&
          FILTER (
                  ALL ( DimDate[Date] );
                   DimDate[Date] >= Anterior3Meses
                   && DimDate[Date] <= MAX ( DimDate[Date] )
                
                                ));FactAnswers[Dpaci_sq]; FactAnswers[Dpaci_sq])
    ))

       

       

       

      • v-cherch-msft's avatar
        v-cherch-msft
        Microsoft Employee
        8 years ago

        Hi EvertonRamone

         

        It’s better to share some simplified data sample and expected output. You can upload it to OneDrive or Dropbox and post the link here.

         

        Regards,

        Cherie