cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Anonymous
Not applicable

## Weighed Value based on availability in given period

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?

1 ACCEPTED SOLUTION
Community Support

Hi @Anonymous ,

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

``````Measure =
VAR _a =
SELECTCOLUMNS ( 'DimTid', "Date", [Date] )
VAR _b =
VAR _c =
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.

6 REPLIES 6
Community Support

Hi @Anonymous ,

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

``````Measure =
VAR _a =
SELECTCOLUMNS ( 'DimTid', "Date", [Date] )
VAR _b =
VAR _c =
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
Not applicable

@v-jianboli-msft

Thanks. That did the trick.

Appriciate the help.

Community Support

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?

Refer to:

How to provide sample data in the Power BI Forum

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
Not applicable

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.

Community Support

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
Not applicable

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

Weigh - Logistik aktiviteter

The tables got relations between eachother.

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

``````Measure =

Best regards

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors