Forum Discussion

EvertonRamone's avatar
EvertonRamone
Icon for Helper I rankHelper 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 s...
Need Help
Zubair_Muhammad's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity 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
Icon for Helper I rankHelper 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
    Icon for Microsoft Employee rankMicrosoft 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