Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
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
User | Count |
---|---|
139 | |
113 | |
103 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |