topic Re: Dax measure to create a new dimension in my model in DAX Commands and Tips

Dax measure to create a new dimension in my model

Maltaparis — Tue, 13 Aug 2024 11:03:02 GMT

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

Re: Dax measure to create a new dimension in my model

Rupak_bi — Tue, 13 Aug 2024 11:58:21 GMT

Hi,

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

Re: Dax measure to create a new dimension in my model

Maltaparis — Tue, 13 Aug 2024 12:33:02 GMT

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

Re: Dax measure to create a new dimension in my model

Anonymous — Wed, 14 Aug 2024 02:17:22 GMT

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.

Re: Dax measure to create a new dimension in my model

Maltaparis — Wed, 14 Aug 2024 07:56:36 GMT

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