Forum Discussion
DAX - Measure count active products
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
Zubair_Muhammad,
Really nice. But I couldn't make it in my model:
Look, I have these tables:
| FactSales | |||
| KeyDate | KeyCustomer | KeyProduct | Total |
| 1 | 1 | 1 | 12,9 |
| 1 | 2 | 2 | 13 |
| 1 | 3 | 1 | 156,4 |
| 1 | 4 | 1 | 564,8 |
| 2 | 1 | 1 | 894,8 |
| 2 | 2 | 1 | 56,5 |
| 3 | 1 | 2 | 564,85 |
| 3 | 2 | 3 | 564,8 |
| 4 | 1 | 1 | 1325,6 |
| 4 | 2 | 1 | 132,3 |
| Customer | |
| KeyCustomer | Name |
| 1 | Jean |
| 2 | Mari |
| 3 | Lisa |
| 4 | Julian |
| 5 | Jhonny |
| Calendar | |
| KeyDate | Date |
| 1 | 01/01/2018 |
| 2 | 02/01/2018 |
| 3 | 01/05/2018 |
| 4 | 01/08/2018 |
| Product | |
| KeyProduct | Product |
| 1 | A |
| 2 | B |
| 3 | C |
Rule:
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
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
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])
))
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
