Conditional measure

[KamilRetkiewicz]

Hi Guys,

I had a following problem with measure:  

YourMeasureName =

var FCFOperations =

    CALCULATE(SUM(YTG3_FCF[AmountEXCANDONEOFF]), YTG3_FCF[CATEGORY] = "FCF Operations including Factoring", YTG3_FCF[SCENARIO] = "Budget")

var FCFOneOffs =

    CALCULATE(SUM(YTG3_FCF[AmountEXCANDONEOFF]), YTG3_FCF[CATEGORY] = "FCF One-offs", YTG3_FCF[SCENARIO] = "Budget")

RETURN

FCFOperations + FCFOneOffs

It is return good value: -134,65 when i use it as a card

But I need use this measure in matrix when [CATEGORY] = "FCF Including Factoring" :

YourMeasureName2 =

IF(

    SELECTEDVALUE (YTG3_FCF[CATEGORY])  = "FCF including Factoring",

    CALCULATE(

        [YourMeasureName]

    ),

    1

)
In the rows i have a values from 'MAPPING_YTG3[Name_ACCOUNT] that are mapped from YTG_3[CATEGORY]. 

Unfortunately, my result in the matrix for FCF Including Factoring does not bring any value. What could be the reason for this?
Thank you in advance for your answer

[Kedar_Pande]

@KamilRetkiewicz 

You can try:

YourMeasureName2 =
IF (
MAX(YTG3_FCF[CATEGORY]) = "FCF Including Factoring",
CALCULATE([YourMeasureName], YTG3_FCF[CATEGORY] = "FCF Including Factoring"),
1
)

You can also try adding a column to debug what values are actually being returned for YTG3_FCF[CATEGORY] in the matrix by using a simpler measure like:

TestMeasure = MAX(YTG3_FCF[CATEGORY])

This will help you understand what category is being evaluated in each row.

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[KamilRetkiewicz]

Now got this.

[danextian]

Hi @KamilRetkiewicz 

Does FCF Including Factoring exist in your fact table or is it just an extra row in MAPPING_YTG3 table? If it doesn't, it shows blank  because it is trying to filter YTG_3 but it doesn't exist there or it doesn't exist in the fact table.  I would try the following:

IF (
    SELECTEDVALUE ( YTG3_FCF[CATEGORY] ) = "FCF including Factoring",
    CALCULATE ( [YourMeasureName], REMOVEFILTERS ( YTG3_FCF[CATEGORY] ) ),
    1
)

OR 


IF (
    SELECTEDVALUE ( YTG3_FCF[CATEGORY] ) = "FCF including Factoring",
    CALCULATE ( [YourMeasureName], REMOVEFILTERS ( TableNameThatContainsName_Account ) ),
    1
)

OR 

[KamilRetkiewicz]

Hi, 
This is about : MAPPING_YTG3 
In rows we have [Name_ACCOUNT] but measure reffers to YTG3_FCF[CATEGORY] where is all data.

[danextian]

That makes sense. As what I've mentioned,  using a related table to filter another table where the row being filtered doesnt exist will return blank by default. It is looking for either the other "FCF Including Factoring" but it isnt there so you need to use a filter modifier so it returns the desired value. It is like having a Gross Profit row in a table but there is only Revenue and Expense rows in the other tables. The measures will return blank for that  Gross Profit row. The sample formula is already in my previous reply.

[KamilRetkiewicz]

Thank you for your answer. I used both measures and unfortunately in both cases I got a value of 1 

[danextian]

Instead of  Category, please try using Name_Account.

[shafiz_p]

Hi @KamilRetkiewicz  Seems like the SELECTEDVALUE function not returning the expected value. See the difference in i of including word marks in red. Check this differences.

If not solved your problem, please share PBIX file or sample data.

Best Regards,
Shahariar Hafiz

[KamilRetkiewicz]

[KamilRetkiewicz]

Hi,

After fix i have sth like this: 

maybe it is because i took value from another table? Relationship with mapped table is correct, idk what doesnt work

[shafiz_p]

@KamilRetkiewicz  Try below code:

YourMeasureName2 =
IF(
    SELECTEDVALUE (MAPPING_YTG_3[Name_ACCOUNT])  = "FCF Including Factoring",
    [YourMeasureName],
    1
)

Hope this helps!!

If this solved your problem, please accept it as a solution!!

Best Regards,
Shahariar Hafiz

[KamilRetkiewicz]

unfortunately it didn't work:/