Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
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.

AndreasBjrn_1-1671115870558.png

 

Can anyone help me with this?

 

 

1 ACCEPTED SOLUTION
v-jianboli-msft
Community Support
Community Support

Hi @Anonymous ,

 

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

vjianbolimsft_0-1671517569560.png

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:

vjianbolimsft_1-1671517852019.png

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.

View solution in original post

6 REPLIES 6
v-jianboli-msft
Community Support
Community Support

Hi @Anonymous ,

 

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

vjianbolimsft_0-1671517569560.png

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:

vjianbolimsft_1-1671517852019.png

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.

v-jianboli-msft
Community Support
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?

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

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:

 

AndreasBjrn_0-1671442344570.png

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.

 

AndreasBjrn_1-1671442699925.png

 

Below is a picture of my data model:

 

AndreasBjrn_2-1671442931752.png

 

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

 

v-jianboli-msft
Community Support
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:

vjianbolimsft_0-1671156949734.png

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

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.

AndreasBjrn_0-1671187916052.png

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

 

AndreasBjrn_1-1671188087942.png

 

Best regards

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors