Need to write a measure to use in a card visual

[OlegV]

Hello,

I wrote a measure 'GetJob', which gets values from another table. It works.

But I am stuck at counting distinct values based on this measure to use it in the card visual. I can use only a measure, I cannot create additional calculated tables, columns or create a relationship.

Could you please help me. I've attached a sample pbi file, please download it from my onedrive

Sample

[Daniel29195]

@OlegV 

output 

steps : 

create following columns in each table : 

 in salesorders : 

link = SalesOrders[SalesOrder] & "|" & SalesOrders[SalesOrderLine]

in WIPjobs

link = WIPjobs[SalesOrder] & "|" & WIPjobs[SalesOrderLine]

create a link between the 2 tables : 

now create this measure for the card : 

GetJob = 

var ds = 
ADDCOLUMNS(
    VALUES(SalesOrders[SalesOrder]),
    "c", 
    CALCULATE(
        DISTINCTCOUNT('WIPjobs'[Job])
    )
)

and for the table : 

GetJob  new = 

var ds = 
ADDCOLUMNS(
    VALUES(SalesOrders[SalesOrder]),
    "c", 
    CALCULATE(
        MAX('WIPjobs'[Job])
    )
)

return CONCATENATEX(ds,[c])

let me know if this helps ./

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠:

[OlegV]

@Daniel29195 
thank you for a reply, but 
firstly, I cannot create additional tables, columns or create relationships. I need to achieve the result using a measure only.
secondly, there should be 3 unique Jobs, not 5. Because SalesOrder "C" is filtered out.

[Daniel29195 · Accepted solution]

@OlegV 

GetJob = 

var ds = 
ADDCOLUMNS(
    FILTER(
    SalesOrders,
    NOT ISBLANK('SalesOrders'[StockCode]) &&
    NOT 'SalesOrders'[StockCode] = "    " &&
    'SalesOrders'[Quantity]<>0 &&
    'SalesOrders'[Status] in {"1","2","4"}
    ),
    
    "c" , 
    var c = SalesOrders[Company]
    var s = SalesOrders[SalesOrder]
    var so = SalesOrders[SalesOrderLine]
    return
    CALCULATE(
        DISTINCTCOUNT(WIPjobs[Job]),
        'WIPjobs'[Company]= c,
    'WIPjobs'[SalesOrder]= s,
    'WIPjobs'[SalesOrderLine]=so
    )
)

return SUMX(ds, [c])

    

this code should work . 

the output =  3  . 

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠: 

[OlegV]

Wow, it's worked!

I would ve never come up with this solution and did not find any hint also.

Thank you! Will study your code.