cancel
Showing results 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

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

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
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.

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 😉

New Member

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%

Super User

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

New Member

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]))

Super User

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

New Member
 Customer Group Customer Code Product Number Measure_Freq Measure2 IXX 3760 8670500.8404.23049 0.13 8240200.9101.02400,8240300.9101.02400 IXX 8344 8670500.8404.23049 0.13 8240200.9101.02400,8240300.9101.02400 IXX 8344 8273200.9101.02400 0.07 IXX 48585 8254300.9154.23049 0.07 IXX 8344 8253100.8001.02400 0.07 IXX 1301 8253000.8001.23050 0.07 IXX 1301 8253000.8001.02400 0.07 IXX 8344 8240602.9101.02400 0.07 IXX 8344 8240400.9101.23050 0.07 IXX 14753 8240300.9101.02400 0.13 8240200.9101.02400,8670500.8404.23049 IXX 8344 8240300.9101.02400 0.13 8240200.9101.02400,8670500.8404.23049 IXX 3208 8240200.9101.02400 0.13 8240300.9101.02400,8670500.8404.23049 IXX 49220 8240100.9101.02400 0.07 IXX 8344 8240200.9101.02400 0.13 8240300.9101.02400,8670500.8404.23049 IXX 8344 8228700.0000.00000 0.07
New Member
 Customer Group Customer Code Product Number Measure_Freq Measure2 IXX 3760 8670662.6240.02400 0.07 IXX 8344 8670500.8404.23049 0.07 IXX 8344 8273200.9101.02400 0.07 IXX 48585 8254300.9154.23049 0.07 IXX 8344 8253100.8001.02400 0.07 IXX 1301 8253000.8001.23050 0.07 IXX 1301 8253000.8001.02400 0.07 IXX 8344 8240602.9101.02400 0.07 IXX 8344 8240400.9101.23050 0.07 IXX 14753 8240300.9101.02400 0.13 8240200.9101.02400 IXX 8344 8240300.9101.02400 0.13 8240200.9101.02400 IXX 3208 8240200.9101.02400 0.13 8240300.9101.02400 IXX 49220 8240100.9101.02400 0.07 IXX 8344 8240200.9101.02400 0.13 8240300.9101.02400 IXX 8344 8228700.0000.00000 0.07