topic DAX to give the Products in selected Customer Group with frequency greater than 0.1 in DAX Commands and Tips

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

PowerBIExpert — Fri, 05 Jul 2024 14:00:28 GMT

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

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

Sergii24 — Fri, 05 Jul 2024 14:57:14 GMT

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 😉

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

foodd — Fri, 05 Jul 2024 14:59:50 GMT

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.

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

PowerBIExpert — Fri, 05 Jul 2024 15:12:27 GMT

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

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

PowerBIExpert — Fri, 05 Jul 2024 15:13:20 GMT

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

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

PowerBIExpert — Fri, 05 Jul 2024 15:25:56 GMT

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%

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

lbendlin — Fri, 05 Jul 2024 20:15:24 GMT

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

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

PowerBIExpert — Wed, 10 Jul 2024 16:41:26 GMT

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

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

lbendlin — Wed, 10 Jul 2024 17:25:16 GMT

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