Skip and use relationship in one measure

Melissa · November 2, 2020, 11:19am

If no other Expert picked this up I’d be happy to take a look when I get home tonight.
@BrianJ is the file in post # 7 the most recent or can you share an updated version?

Thanks

wojteq · November 2, 2020, 12:58pm

Yes, that is the most recent file.

To sum up, there are such problems:

Total of all branches is wrong because result of od11 is not included; I tried to solve it by using SUMMARIZE but results were very bad.
There can be situations that contract is assign to one branch, branch who work for that contract is another one but I have to assing that cost to yet another branch I write about it in post nr 10. I tried to solve that problem with using another USERELATIONSHIP but it does not work. When I make it as another measure that use USERELATIONSHIP it of course work, but when there are two USERELATIONSHIP, measure use only one of them. I would like it to work like: when X situation use that relationship but when contract ID is Y use another relationship.

In this measure I would like have if statement and two USERELATIONSHIP functions. That was my idea to solve that problem

Edit/
I should clarify informations. The point is not so much about the contract number, but about assigning a given cost to a different contract than it appears from the data. Because there are situations where only few costs from specific contract have to be assign to another contract. Is it even possible?

Melissa · November 2, 2020, 5:21pm

Hi @wojteq,

I’ve got a solution for you but also want to note that I have concerns about moving costs around like this. That said, this is what I’ve got for you.

Total Costs wFILTER v2 = 
VAR others =
CALCULATE( [Total Costs 2] ,
    FILTER( VALUES( FactCosts[CONTRACT TYPE] ), 
        FactCosts[CONTRACT TYPE] IN {"KK", "KD", "KP"}
    ),
    FILTER( VALUES( FactCosts[CONTRACT_ID] ),
        NOT( FactCosts[CONTRACT_ID] ) IN {"KK200074", "KK300004", "KK300005", "KP000184", "KP000156", "KP000163", "KP000181", "KP000182", "KP000185", "KP000180", "KP000178", "KP000160"}
    ),
    FILTER( VALUES( FactCosts[CONTRACT_ID]),
        FactCosts[CONTRACT_ID] <> "KK200073" 
    )
)
VAR MoveContract =
    CALCULATE(
        CALCULATE( [Total Costs 2],
            FILTER( VALUES( FactCosts[CONTRACT_ID]),
                FactCosts[CONTRACT_ID] = "KK200073"
            )
        ),  ALL( Branch[BRANCH_NAME] )
    )
VAR CALCos11 =
CALCULATE(
    CALCULATE( [Total Costs 2],
        USERELATIONSHIP( FactCosts[BRANCH_NAME_WHO_WORK], Branch[BRANCH_NAME2] )
    ), FILTER( VALUES( Branch[BRANCH_NAME] ),
        Branch[BRANCH_NAME] = "od11"
    )
)

VAR os11 =
IF( SELECTEDVALUE(Branch[BRANCH_NAME]) = "od11", CALCos11 + MoveContract,
    IF( NOT( ISINSCOPE( Branch[BRANCH_NAME] )), CALCos11 + MoveContract )
)

VAR Result =
    SUMX(
        ADDCOLUMNS( VALUES( Branch[BRANCH_NAME] ),
            "@Value", IF( SELECTEDVALUE( Branch[BRANCH_NAME2] ) = "os11", os11, others )
        ), [@Value]
    )
RETURN

IF( ISINSCOPE( Branch[BRANCH_NAME] ), 
    IF( SELECTEDVALUE( Branch[BRANCH_NAME2] ) = "os11", os11, others ),
    os11 + others
)

.

With this result.

If anyone has a better solution, I’m all ears.

Here’s your sample file. eDNA - Test data.pbix (1.0 MB)
I hope this is helpful.

wojteq · November 2, 2020, 6:59pm

Omg @Melissa thank you so much!!! This look amazing and too much for my actual knowledge I would never figure it out.

I’ve also negative attitude about that way of assinging costs and I’m trying to explain my directors that it should be solved at the moment of placing data in ERP program.

Also @BrianJ thank you for trying too!

EnterpriseDNA · November 3, 2020, 2:38am

Hi @wojteq, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

wojteq · November 3, 2020, 10:07am

@Melissa why did u make variable “Result” which is not used?

I’m looking at total values because in only 2 months total is wrong, example:

Rest months are ok.

Melissa · November 3, 2020, 10:16am

Just forgot to remove that… it’s usually my go to method for fixing incorrect totals but was not working properly in this specific case. Will take another look, when I get the chance.

Is it still the one exception CONTRACT_ID = “KK200073” ?

wojteq · November 3, 2020, 10:20am

Yes, I’ve added one more exception but those wrong totals are the same in my measure and in yours beacuse that new exception does not appear in those months.

Melissa · November 3, 2020, 10:40am

@wojteq,

So I have something to check against, what months are you looking at? Is that Jan, Feb and Mar?
Also can you share the depicted results in an excel file? That’s easier to work with…

Thanks!

wojteq · November 3, 2020, 2:45pm

In the pbi file you have upload here, wrong total result is for: Jan, Feb, Mar, Apr. In my excel file there are different results because I’ve got more exception. We can work on the file u already have.

Is there a possibility to edit my older posts? I would like to delete images with data and pbix file from all posts.

Melissa · November 3, 2020, 2:50pm

Perfect!
Please provide the expected outcomes for those months in excel so I have a value to check against.

RE: Edit post
I believe you can with the “pencil” icon

wojteq · November 3, 2020, 2:55pm

data.xlsb (15.2 KB)

Unfortunetly I can’t edit old posts, only the latest. Are you a moderator on this forum?

Melissa · November 3, 2020, 4:57pm

Hi @wojteq,

This is an open forum, intended so others can learn from previous posted questions but I’ll ask the @EnterpriseDNA team. Please see request below, can you help this member out?

.

As for my second attempt, this is what I’ve got for you (there seems to be a rounding difference) but I don’t have the time to look into that, so I hope that won’t be present in your actual file.

Total Costs wFILTER v3 = 
VAR vTable = 
ADDCOLUMNS(
    GENERATE( VALUES( Branch[BRANCH_NAME] ), VALUES( 'Calendar'[MonthnYear] )),
    "@Others", IF( [BRANCH_NAME] <>"od11",
        CALCULATE( [Total Costs 2] ,
            FILTER( VALUES( FactCosts[CONTRACT TYPE] ), 
                FactCosts[CONTRACT TYPE] IN {"KK", "KD", "KP"}
            ),
            FILTER( VALUES( FactCosts[CONTRACT_ID] ),
                NOT( FactCosts[CONTRACT_ID] ) IN 
                {"KK200074", "KK300004", "KK300005", "KP000184", "KP000156", "KP000163", "KP000181", "KP000182", "KP000185", "KP000180", "KP000178", "KP000160"}
            ),
            FILTER( VALUES( FactCosts[CONTRACT_ID]),
                FactCosts[CONTRACT_ID] <> "KK200073" 
            )
        )),
    "@os11", IF( [BRANCH_NAME] ="od11",
        VAR MoveContract =
        CALCULATE(
            CALCULATE( [Total Costs 2],
                FILTER( VALUES( FactCosts[CONTRACT_ID]),
                    FactCosts[CONTRACT_ID] = "KK200073"
                )
            ),  ALL( Branch[BRANCH_NAME] )
        )
        VAR CALCos11 =
        CALCULATE(
            CALCULATE( [Total Costs 2],
                USERELATIONSHIP( FactCosts[BRANCH_NAME_WHO_WORK], Branch[BRANCH_NAME2] )
            ), FILTER( VALUES( Branch[BRANCH_NAME] ),
                Branch[BRANCH_NAME] = "od11"
            )
        ) RETURN MoveContract + CALCos11
    )
)
VAR others = SUMX( vTable, [@Others] )
VAR os11 = SUMX( vTable, [@os11] )
RETURN

others + os11

.

With this result.

Here’s your updated sample file. eDNA - Test data.pbix (1.0 MB)
I hope this is helpful.

wojteq · November 6, 2020, 11:13am

Thanks so much @Melissa! It’s working percet! I add some few more exceptions and it works great!

@EnterpriseDNA I’d like to ask for deleting data and pbix files from this topic, or at least all pbix files. Proposed measures and all important informations about solutions are in the posts. Or if you let me edit my older posts I would just delete few data, not all I wanted to do it on my own but I am not able to edit older posts.