Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
BalaVenuGopal
Resolver I
Resolver I

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

More than 2.png

 

 

 

 

 

1 ACCEPTED SOLUTION
v-jayw-msft
Community Support
Community Support

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.

4.PNG5.PNG

 

Best Regrads,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

2 REPLIES 2
v-jayw-msft
Community Support
Community Support

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.

4.PNG5.PNG

 

Best Regrads,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
tex628
Community Champion
Community Champion

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


Connect on LinkedIn

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.