Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

DAX measure required

Good morning Magnificent Humans,   I have been trying to crack this nut for the past couple days with no luck with my limited experience.   I have 3 tables: 1 Data (multiple columns) and 2 Look u...
Need Help
  • fhill's avatar
    fhill
    6 years ago

    Great, since 'ALL POS' has a 1-1 relationship with both tables, we can build a single 'New Column' on this table to acomplish what you need....  

     

    Vac wo Plan = IF(RELATED('VAC POS'[EmpID]) <> BLANK(), IF(RELATED('WPA POS'[PosNo]) = BLANK(), "Blank VAC wo Plan"))
     
    You can either change the final text to 1, and COUNT the results, or leave it text and 
    Count = COUNTA('All POS'[Vac wo Plan]) the new coulmn as a measure to get your result.
     

fhill's avatar
fhill
Icon for Resident Rockstar rankResident Rockstar
6 years ago

Based on your notes, i'm assuming your table structure looks like this with a Data Table, and 2 'Many to 1' Linked Tables.  (Key point here is there's no connection between EmpID on Table1 & Table 3.  Only 2 different connections to the Data Table.)

 

Take this in 2 easy steps.  1:  Identify if EmpID is present in Table3, and create a Custom Column as such on Table 1...

      Is In Table 3 = LOOKUPVALUE(Table3[Table 3 Data],Table3[EmpID],Table1[EmpID])

 

2:  Then you can use a simple IF statement in the CountRows calcuation....

     Count Table 2 = IF('Table1'[Is In Table 3] = BLANK(),BLANK(), CALCULATE(COUNTROWS('Data Table')))

 

Sample below.  If this isn't right, or there's more details you haven't shared, please post sample clensed data from your Tables, and screen shot of the Relationship Joins.

 

Forrest