Forum Discussion

KamilRetkiewicz's avatar
KamilRetkiewicz
Helper I
1 year ago

Conditional measure

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

 

Need Help

12 Replies

  • shafiz_p's avatar
    shafiz_p
    Super User
    1 year ago

    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's avatar
      KamilRetkiewicz
      Helper I
      1 year ago

      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's avatar
        shafiz_p
        Super User
        1 year ago

        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

  • danextian's avatar
    danextian
    Super User
    1 year ago

    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's avatar
      KamilRetkiewicz
      Helper I
      1 year ago

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

       

       

      • danextian's avatar
        danextian
        Super User
        1 year ago

        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.

  • Kedar_Pande's avatar
    Kedar_Pande
    Super User
    1 year ago

    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