Find and Repeat Value

Carlo1975 · ‎12-14-2020

Hi, I have the same topic in Power Query and the reply about mahoneypat it's ok, it works in power query but then, when I go to update power BI it doesn't work anymore (twice I blocked the update after 10 hours). So, I would to know if a can do it with DAX (maybe it's better)

I try to explain, I have a table with 3 columns ordered for IdLotto and Use (in power query, I don't know if is the same after in DAX):

Index	IdLotto	Use
1	00000070_LOI	1 - Residential
2	00000071_LOI	1 - Residential
3	00000072_LOI	1 - Residential
4	00000074_LOI	1 - Residential
5	00000075_LOI	2 - Hospitality
6	00000075_LOI	3 - Industrial
7	00000086_LOI	1 - Residential
8	00000086_LOI	3 - Industrial
9	00000094_LOI	1 - Residential
10	00000094_LOI	6 - Parking
11	00000094_LOI	8 - Other
12	00000098_LOI	1 - Residential
13	00000100_LOI	6 - Parking

I have to add a new column. For each IdLotto (which can be repeated), I have to insert in the new column the first value find in Use (the order I do for "IdLotto" and "use" allows me to have the "Use" column always sorted by priority through the initial number).

At the end I will have a table like this:

Index	IdLotto	Use	Use_For_Lot
1	00000070_LOI	1 - Residential	1 - Residential
2	00000071_LOI	1 - Residential	1 - Residential
3	00000072_LOI	1 - Residential	1 - Residential
4	00000074_LOI	1 - Residential	1 - Residential
5	00000075_LOI	2 - Hospitality	2 - Hospitality
6	00000075_LOI	3 - Industrial	2 - Hospitality
7	00000086_LOI	1 - Residential	1 - Residential
8	00000086_LOI	3 - Industrial	1 - Residential
9	00000094_LOI	1 - Residential	1 - Residential
10	00000094_LOI	6 - Parking	1 - Residential
11	00000094_LOI	8 - Other	1 - Residential
12	00000098_LOI	1 - Residential	1 - Residential
13	00000100_LOI	6 - Parking	6 - Parking

and so for IdLotto 00000075_LOI (bold) I will write 2 - Hospitlity on all the 2 row of ID 00000075_LOI.

For IdLotto 00000094_LOI I will write 1 - Residential

It's possible to do it with DAX?

Thank you

Carlo

mahoneypat · ‎12-14-2020

I think I suggested a DAX column in the last post. While a Table.Buffer step would help that one dramatically, this is best done with DAX. Here is a column expression for it.

Use For Lot =
CALCULATE (
    FIRSTNONBLANKVALUE (
        Buildings[Index],
        MIN ( Buildings[Use] )
    ),
    ALLEXCEPT (
        Buildings,
        Buildings[IdLotto]
    )
)