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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
PowerBIExpert
New Member

DAX to give the Products in selected Customer Group with frequency greater than 0.1

Hi Community
Need help for the below logic - 
DAX to give the Products in selected Customer Group with frequency greater than 0.1 but it shall be according to the Customer Code which is not associated, it shall give the product number(s) (Concatenated list in case more than 1) of the other customer no. with frequency greater than 0.10
and here frequency must be calculated based on Customer Group and must not get impacted by Customer Code.
I need the result as shown in Measure2

PowerBIExpert_0-1720185400965.png

Here Customer Group = IXX is selected in the slicer
Here is the measure for frequency,
Measure_freq = 

CALCULATE(count(Table[Product Number]),ALL(Table[Customer Code]))/CALCULATE(count(Table[Product Number]),ALLEXCEPT(Table,Table[CustomerGroup]))
I have tried the below logic got partial result but not full result

NotSelectedCC_freq =
VAR SelectedCustomerGroup = SELECTEDVALUE('Table'[Customer Group])
VAR SelectedCustomerCode = SELECTEDVALUE(Table[Customer Code])
VAR _tab1 =
CALCULATETABLE(
VALUES(Table[Product Number]),
FILTER(
ALLSELECTED( Table),
Table[Customer Code] <> SelectedCustomerCode &&
Table[Customer Group] = SelectedCustomerGroup
)
)
var t =CALCULATE(count(Table[Product Number]),ALL(Table[Customer Code]))/CALCULATE(count(Table[Product Number]),ALLEXCEPT(Table,Table[Customer Group]))
var ratio5 = IF (
ISFILTERED ('Table'[Customer Group]), t)

var r6 = calculate(maxx(all(Table[Customer Group]),ratio5))


-- Get Product_numbers that are not in the selected CustomerCode but within the selected Customer Group

 

VAR Product_numbersInNotSelectedCustomerCodes =
CALCULATETABLE(
VALUES(Table[Product Number]),
FILTER(
all(Table[Product Number]),Table[Product Number] in _tab1 && Table[Product Number] <> SELECTEDVALUE(Table[Product Number])

&&
r6 >= 0.10

)
)

var Result =
if([Item freq2] >= 0.10,

CONCATENATEX(Product_numbersInNotSelectedCustomerCodes, Table[Product Number],","))

RETURN
Result

8 REPLIES 8
foodd
Super User
Super User


How to share a file as part of a Forum Post?
You can upload files to a cloud storage service such as OneDrive, Google Drive, Dropbox, or to a GitHub repository, and then share the file’s URL.

Sergii24
Super User
Super User

Hi @PowerBIExpert, I'm not sure I clearly understood what you want to achieve... Could you elaborate more on it step-by-step? You tell that you want a result of measure 2 but before saying about concatenation (which is not present at a screenshot) and then non associated Customer Code (why this infromation is imporant?). Please provide as many details as possible to describe what you want to achieve and what your data model is.

P.S. as I always ask, make sure you provide a sample to copy-paste, not a screenshot (if you really want someone dedicate their time to help you 😉 

So the basic idea is to get all the product numbers as recommendations which has frequency (occurence in data for a customer group irrespective of customer code) > = 0.10 (10%)  in front of Customer Group, Customer Code and Product Number columns and since it will be the recommendation so dont want to display the same product number there,
so in data you can see if Product number is 8670500.8404.23049 it appears 2 times for IXX Customer Group so its frequncy is 2/15 = 0.13 hence its required to be shown as recommendation in front of other product numbers and similarly as a recommendation to this product number 8670500.8404.23049, other product numbers are shown which is 8240200.9101.02400,8240300.9101.02400
so in layman terms regard this as a hierarchy of Category(CustomerGroup in our case), Subcategory(Customer Code in our case) and Brand(Product No in our case) so suppose a customer buys a nike Tshirt (Category - Clothing, Subcategory - Sports, Brand - Nike) you would like to show the customer the recommendation of other brand Clothings irrespective of Subcategory only if the frequency of purchace is more than 10%

 

lbendlin_0-1720210446631.png

like this? Doesn't sound like a useful strategy though as it is bias-affirming.

Hi @lbendlin thanks for responding but Measure_Freq is a measure that I have created and its not a column of the table.
Measure is written as - 
CALCULATE(count(Table[Product Number]),ALL(Table[Customer Code]))/CALCULATE(count(Table[Product Number]),ALLEXCEPT(Table,Table[Customer Group]))

"Max of Measure_Freq"  indicates that is a column. You can't MAX a measure.

Customer Group Customer CodeProduct NumberMeasure_FreqMeasure2
IXX37608670500.8404.230490.138240200.9101.02400,8240300.9101.02400
IXX83448670500.8404.230490.138240200.9101.02400,8240300.9101.02400
IXX83448273200.9101.024000.07 
IXX485858254300.9154.230490.07 
IXX83448253100.8001.024000.07 
IXX13018253000.8001.230500.07 
IXX13018253000.8001.024000.07 
IXX83448240602.9101.024000.07 
IXX83448240400.9101.230500.07 
IXX147538240300.9101.024000.138240200.9101.02400,8670500.8404.23049
IXX83448240300.9101.024000.138240200.9101.02400,8670500.8404.23049
IXX32088240200.9101.024000.138240300.9101.02400,8670500.8404.23049
IXX492208240100.9101.024000.07 
IXX83448240200.9101.024000.138240300.9101.02400,8670500.8404.23049
IXX83448228700.0000.000000.07 

Customer Group Customer CodeProduct NumberMeasure_FreqMeasure2
IXX37608670662.6240.024000.07 
IXX83448670500.8404.230490.07 
IXX83448273200.9101.024000.07 
IXX485858254300.9154.230490.07 
IXX83448253100.8001.024000.07 
IXX13018253000.8001.230500.07 
IXX13018253000.8001.024000.07 
IXX83448240602.9101.024000.07 
IXX83448240400.9101.230500.07 
IXX147538240300.9101.024000.138240200.9101.02400
IXX83448240300.9101.024000.138240200.9101.02400
IXX32088240200.9101.024000.138240300.9101.02400
IXX492208240100.9101.024000.07 
IXX83448240200.9101.024000.138240300.9101.02400
IXX83448228700.0000.000000.07 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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