Forum Discussion

BalaVenuGopal's avatar
BalaVenuGopal
Icon for Resolver I rankResolver I
6 years ago
Solved

How to use measure in filter dax

Hi Team,

Here is my data , i would like to create a measure which should calcualte no of uses who have purchased more than 1 prouduct dividede by total numbe of uses for the selected month 

IF i choose multiple months am expecting the same behaviour for the selectd months [Total uses who bought more than 1 proudct / Total no of uses for selcted moths]

Sample data with filters 

 

 DATA                              TABLE REPORT                                           SLICER

 

 

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi BalaVenuGopal ,

     

    You will need to create a calculated table as slicer.

    Table 2 = DISTINCT('Table'[MONTH])

    Check the following measures.

    Measure = 
var a = CALCULATE(DISTINCTCOUNT('Table'[PRODUCT]),FILTER(ALLEXCEPT('Table','Table'[USER],'Table'[SUB DT]),'Table'[MONTH] in VALUES('Table 2'[MONTH])))
return
MAXX(ALLEXCEPT('Table','Table'[USER]),a)

Measure 2 = CALCULATE(DISTINCTCOUNT('Table'[USER]),FILTER('Table',[Measure]>1))/CALCULATE(DISTINCTCOUNT('Table'[USER]),FILTER('Table','Table'[MONTH] in VALUES('Table 2'[MONTH])))

    Result would be shown as below.

     

    Best Regrads,

    Jay

2 Replies

  • tex628's avatar
    tex628
    Icon for Community Champion rankCommunity Champion
    6 years ago

    If im understanding you correctly, from your example you're looking for the result 1/7? (14%)

    If this is the case the following measure should work:

    Measure = 
DIVIDE(
CALCUlATE( COUNTROWS( SUMMARIZE ( Table , [Users] , "Count of products" , COUNT(Table[Products])) , [Count of products] <> 1 ) ,
COUNTROWS( SUMMARIZE ( Table , [Users] , "Count of products" , COUNT(Table[Products])) , BLANK())


    Br, 
    J

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi BalaVenuGopal ,

     

    You will need to create a calculated table as slicer.

    Table 2 = DISTINCT('Table'[MONTH])

    Check the following measures.

    Measure = 
var a = CALCULATE(DISTINCTCOUNT('Table'[PRODUCT]),FILTER(ALLEXCEPT('Table','Table'[USER],'Table'[SUB DT]),'Table'[MONTH] in VALUES('Table 2'[MONTH])))
return
MAXX(ALLEXCEPT('Table','Table'[USER]),a)

Measure 2 = CALCULATE(DISTINCTCOUNT('Table'[USER]),FILTER('Table',[Measure]>1))/CALCULATE(DISTINCTCOUNT('Table'[USER]),FILTER('Table','Table'[MONTH] in VALUES('Table 2'[MONTH])))

    Result would be shown as below.

     

    Best Regrads,

    Jay