add column indicating if there is an entry in another table

[hanswittoeck]

Hi there

Table DIM_PM_order has the fields

Orderno
startdate

enddate

......

Table F_PM_cost_details has fields

Orderno 
materialno

qty

cost

I want to add a field in table DIM_PM_order 'HasMaterial' that indicates if there is at least 1 entry in table F_PM_cost_details.

The tables are all directquery tables. 

any suggestions?

thx

[hanswittoeck]

finally, I went for the measure solution

[Ashish_Mathur]

Hi,

Try this calculated column formula

HasMaterial = calculate(countrows(F_PM_Cost-Details),filter(F_PM_Cost-Details,F_PM_Cost-Details[OrderNo]=earlier(Dim_PM_Order[OrderNo])))

Hope this helps.

[hanswittoeck]

Thank you for your assitance Ashish

but unfortunatly i get an error
Function 'CALCULATE' is not allowed as part of calculated column DAX expressions on DirectQuery models.

[v-heq-msft]

Hi @hanswittoeck ,
You can try this

IsValueInAnotherTable = 
IF(
    NOT(ISBLANK(RELATED(F_PM_cost_details[Orderno]))),
    TRUE,
    FALSE
)

Ensure there is a relatiopnship between two tables

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[v-heq-msft]

Hi @hanswittoeck ,
Thanks to Idrissshatila reply.
You can try this
Sample data
DIM_PM_order

F_PM_cost_details

OrdernostartdateenddateHasMaterial

Create a column

HasMaterial = 
IF(
    COUNTROWS(
        FILTER(
            F_PM_cost_details,
            F_PM_cost_details[Orderno] = DIM_PM_order[Orderno]
        )
    ) > 0,
    TRUE,
    FALSE
)

Final output

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[hanswittoeck]

thank you for your assistance and elaborate response

but in directquery, there are limitations

Function 'COUNTROWS' is not allowed as part of calculated column DAX expressions on DirectQuery models.

[Idrissshatila · Accepted solution]

Hello @hanswittoeck ,

if the tables are direct query then they're not stored in power bi, they are retreived on query level so in order to do what you want it's either you need to do it in the source or you do a measure and display it in a table visual.

[hanswittoeck]

My intention is to add a calculated column for this

[hanswittoeck]

Dear @Idrissshatila The choice for the calculated column is because I cannot change the table designs, and more over, I can think of more possible columns to add as I explore more reporting options.

• Is there labour involved in the orders?
•  Where orders planned?
• did ordes exceed the budget?
• ...

It would be nice to have info like this to filter the orders.

So if i have a technique to add a column, it will help me a lot and simplify my visual creation