Weighed Value based on availability in given period

[Anonymous]

Hello everyone.

Im looking for a dax formula that can provide me with the yellow column in the below picture, but I dont have the dax brain to figure out how to formulate it. I need to use this in either a measure or column in Power BI. I have all the data formatted green.

I want to allocate a role to a location based on the weight i've set. So if weigh 1 is available in the given week it will return that in the corresponding column and leave nothing in the rest of the given columns in that location.

Can anyone help me with this?

[v-jianboli-msft · Accepted solution]

Hi @Anonymous ,

Based on your description, I have created a simple sample(for more details, please refer to the PBIX file):

Please try:

Measure =
VAR _a =
    SELECTCOLUMNS ( 'DimTid', "Date", [Date] )
VAR _b =
    MAX ( 'AstaData'[Location] )
VAR _c =
    ADDCOLUMNS (
        FILTER ( ALL ( AstaData ), [Date] IN _a && [Location] = _b ),
        "WEIGHT",
            MAXX (
                FILTER ( ALL ( 'Logistik aktiviteter' ), [ID] = EARLIER ( AstaData[ID] ) ),
                [Weight]
            )
    )
VAR _d =
    MINX ( _c, [WEIGHT] )
RETURN
    IF ( MAX ( 'Logistik aktiviteter'[Weight] ) = _d, MAX ( 'AstaData'[Role] ) )

Final output:

Best Regards,

Jianbo Li

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

[Anonymous]

@v-jianboli-msft 

Thanks. That did the trick. 

Appriciate the help.

[v-jianboli-msft]

Hi @Anonymous ,

Sorry, still don't quite understand the logic of your calculation, can you explain it in more detail? Or is my understanding correct? You mentioned that all three tables have relationships with each other, so what kind of relationships do they have and which fields do they rely on to establish the relationships?

Sorry for that the information you have provided is not making the problem clear to me. Can you please share more details to help us clarify your scenario?

Please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

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

[Anonymous]

Hi @v-jianboli-msft 

Thanks for your answer again. Its much appriciated.

I will try to explain my self better. 

My problem is:

I got 48 locations. Im useing a year and week slicer to find what activies are going on, across all locations, in the given timeperiod. This gives me the following:

Each activity has a contracter related to it (Con1, Con2 and Con3 in the above), a unique ID (Even though they have the same name) and a weight. I need a measure that will find the activity with the lowest weight based on location and return the contracter related and return blank on activities with a higher weight in the same location. On the following picture im trying to descripe that. The yellow column is the measure I need.

Below is a picture of my data model:

I hope this explains it better. If not, I will send you the pbix file.

[v-jianboli-msft]

Hi @Anonymous ,

According to your description, you only mention that the corresponding role is returned at a weight of 1, but in the figure you provided, it seems that the role is also returned at a weight of 6. What is the exact logic of its calculation?
If it is the case that the corresponding role is returned when the weight is minimal, please try:

Measure = 
var _a = CALCULATE(MIN('Table'[Weigh]),ALLEXCEPT('Table','Table'[Week],'Table'[Location]))
return IF(MAX('Table'[Weigh])=_a,MAX('Table'[Role]),BLANK())

Output:

If not, please clarify the calculation logic.

Best Regards,

Jianbo Li

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

[Anonymous]

Hi @v-jianboli-msft 

Thanks for your answer!

You are right about the logic is wrong in the figure I provided. Sorry about that.
If the same name are in two locations, it would have same weigh. In the example I provided the names should have been NameD, Name E and Name F in location B. Im not sure if that changes anything about the measure you made?

I've had a look at your pbix and that work as intened. However I can't get it to work with my own data. Is that because im getting data from different tabels?

I get my data from:

Week(Uge) - DimTid
Name(Navn) - AstaData

Location - AstaData

Role (Entreprenør) - AstaData

Weigh - Logistik aktiviteter

The tables got relations between eachother.

But when I insert the measure it doesen't work.

Measure = 
var _a = CALCULATE(MIN('Logistik Aktiviteter'[Weight]),ALLEXCEPT(AstaData,DimTid[Uge],AstaData[Lokation]))
return IF(MAX('Logistik Aktiviteter'[Weight])=_a,MAX(AstaData[Entreprenør]),blank())

Best regards