Dax measure to create a new dimension in my model

[Maltaparis]

Hello everyone,

I'm reaching out to ask for your assistance in creating a DAX measure

In my data modl below, I would like a DAX measure that basically recreates the dimension Item in my data model, but categorizying it with the following rule :

• If only item "A" is sold for a unique ID client, then the measure should display "A"
• If only item "B" is sold for a unique ID client, then the measure should display "B"
• If both items "A" and "B" are sold, then show "Mixed".

Thus, it will allows me to analyze my data with this new category. I cant add a static new calulated column because I want my measure to be sensitive to the time slicer.

Client ID	Date	Item	Quantity	Price	Amount
1	08/12/2024	A	1	10	10
2	08/12/2024	A	2	10	20
2	08/12/2024	B	4	20	80
3	08/12/2024	A	3	10	30
4	08/12/2024	B	1	20	20
4	08/12/2024	B	2	20	40
5	08/12/2024	A	4	10	40
6	08/12/2024	B	6	20	120

With this, I should be able to generate a PivotTable that would display, for example, 6 unique clients with 2 classified as "Mixed", 3 as "A", and 1 as "B". I would also like to associate the numeric columns (Amount and Quantity) with this new classification.

I’ve tried to implement this, but I’m having difficulty. Below is my current approach. If anyone could help, I’d really appreciate it.

Also, I woul like , to add  the lastyearview (to identify for instance who was only A or B and now is Mixte)

Thank you in advance for your strong support!

VAR ClientArticles =
    CALCULATETABLE (
        VALUES ( 'Table'[Item] ),
        ALLEXCEPT ( 'Table', 'Table'[Client id] )
    )
RETURN
    SWITCH (
        TRUE (),
        COUNTROWS ( ClientArticles ) > 1, "Mixte",
        COUNTROWS ( ClientArticles ) = 1
            && MAX ( 'Table'[Item] ) = "A", "A",
        COUNTROWS ( ClientArticles ) = 1
            && MAX ( 'Table'[Item] ) = "B", "B",
        "Autre"
    )

[Anonymous · Accepted solution]

Hi, @Maltaparis 

You can try the following methods.

Measure = 
Var _count=CALCULATE(DISTINCTCOUNT('Table'[Item]),ALLEXCEPT('Table','Table'[Client ID]))
RETURN
SWITCH(TRUE(),
MAX('Table'[Item])="A"&&_count=1,"A",
MAX('Table'[Item])="B"&&_count=1,"B",
"Mixed")

Create a new calculation table.

Table 2 = SUMMARIZE('Table','Table'[Client ID],"Type",[Measure])

Is this the result you expected?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Maltaparis · Accepted solution]

Yes it's what I am looking for!

Alsow I will.add a lastyearsameperiod function to analyse vs lastyear.

Thank you all for yout help

[Rupak_bi]

Hi,

Please see below approach. If this solves your purpose, please accept as solution.

[Maltaparis]

Hi Rupas, 

Thank you for your help, but I would like not to display all the customers id (there are thousands in the dataset)

When I do not show explicitly the id then the pivottable considers everything to be "Mixed"

Let say,  I expected my pivot table to look like that 

Thanks again for your help

Item	Number of customers
A	3
B	2
Mixed	1

[Anonymous · Accepted solution]

Hi, @Maltaparis 

You can try the following methods.

Measure = 
Var _count=CALCULATE(DISTINCTCOUNT('Table'[Item]),ALLEXCEPT('Table','Table'[Client ID]))
RETURN
SWITCH(TRUE(),
MAX('Table'[Item])="A"&&_count=1,"A",
MAX('Table'[Item])="B"&&_count=1,"B",
"Mixed")

Create a new calculation table.

Table 2 = SUMMARIZE('Table','Table'[Client ID],"Type",[Measure])

Is this the result you expected?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.