New Column not reading whole table

[Anonymous]

Hi everyone,

I have two tables in my file that I am working with. I am trying to create a new column in the Quote Report file that takes the product from the Quote Report and matches it will the product from the sales file and returns a value. Below is the current measure I have and it only returns "N/A" even though many of the products do match.

VS = 
VAR _part = 'Quote Report 24'[Product]
return 
IF(
 CALCULATE(COUNTROWS('PBI_Sales_History_Cur_FY_Detail'),FILTER(ALL(PBI_Sales_History_Cur_FY_Detail), 
_part = RELATED(PBI_Sales_History_Cur_FY_Detail[Product]))), RELATED(PBI_Sales_History_Cur_FY_Detail[VALUE]),"N/A")

Thank you!

[v-yalanwu-msft]

Hi, @Anonymous ;

Is the above answer helpful to you? If so, Would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yalanwu-msft]

Hi, @Anonymous ;

I create a simple example and create a measure ( if table A 's product  have matching values in B, then [value] of B is returned; or, return "N / A") ：

VS =
VAR _Pcolumn =
    SUMMARIZE ( 'B', [Product] )
RETURN
    IF (
        MAX ( [Product] ) IN _Pcolumn,
        CALCULATE (
            SELECTEDVALUE ( B[Product] ),
            FILTER ( ALL ( 'B' ), [Product] = MAX ( 'A'[Product] ) )
        ),
        "N/A"
    )

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[selimovd]

Hey @Anonymous ,

your CALCULATE(COUNTROWS... will always return an integer. In your IF statement you check for IF(CALCULATE what always will be FALSE as CALCULATE doesn't return TRUE or FALSE. So it will always show the else condition what is "N/A".

Maybe you want to check if there is a match, so if CALCULATE > 0. If that's the case try the following measure:

VS =
VAR _part = 'Quote Report 24'[Product]
RETURN
    IF(
        CALCULATE(
            COUNTROWS( 'PBI_Sales_History_Cur_FY_Detail' ),
            FILTER(
                ALL( PBI_Sales_History_Cur_FY_Detail ),
                _part = RELATED( PBI_Sales_History_Cur_FY_Detail[Product] )
            )
        ) > 0,
        RELATED( PBI_Sales_History_Cur_FY_Detail[VALUE] ),
        "N/A"
    )

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

Hi @selimovd 
Thank you for the information. I see you mentioned that my CALCULATE(COUNTROWS.. function will always return an integer. The value I want to return is of type text and three letters long no matter what if that matters. 

Is/are there different function(s) that would do this better instead?

Thank you again!

[selimovd]

@Anonymous The value you return at the end is either RELATED( PBI_Sales_History_Cur_FY_Detail[VALUE] ) or "N/A". So if the column [VALUE] is text you will still return text. With the CALCULATE(COUNTROWS(... you only check if there are any values. That approach is totally fine if it returns the result you want.

If you would like to compare to a string you could change the COUNTROWS to MAX. Then you also have to compare to a string:

IF(
    CALCULATE(
        MAX( 'PBI_Sales_History_Cur_FY_Detail'[ColumnWithText] ),
        ...
    ) = "Example Text",
    "Option 1", 
    "Option else"
)

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

@selimovd That makes sense. For whatever reason the only value that is getting returned is still "N/A" despite me knowing that some of the products on both tables match. 

[selimovd]

@Anonymous can you provide a sample file? I think that's easier to investigate than writing in this case