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.
     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Much apreciated Forrest, you sir are a god among men.

Now I have to investigate and learn why your solution works!

Have a great weekend.

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

 

 

Vac wo Plan =               

IF(    --If Statement--

     RELATED(      -- Looks at RELATED Values in Joined Tables--

                      'VAC POS'[EmpID]) <> BLANK()  -- Looking for Non Blank Rows in the EmpID (PosNO) data of the VAC POS Table,                                                                               but because this is Nested inside a REALTED function, it match PosNO to PosNO in both tables.  If there's no matching PosNO value in the REALTED table, a BLANK is returned instead of the RELATED Value.  Therefor <> BLANK() basically looks for ANY RELATED Value in the 2nd table.

        --IF TRUE (finds a non-blank *Related* Value) THEN --                    

                       , IF(    -- Second IF Statement --

                             RELATED(  -- Again, only looking for REALTED values (match PosNO to PosNO) but this time in the WPA POS Table

                                    'WPA POS'[PosNo]) = BLANK()    -- this time Looking for BLANK (Doesn't exist) values.

, "Blank VAC wo Plan"))   -- If True Again we have Non-Blank RELATIONs from the frist table (exists), AND Blank RELATIONs form teh 2nd table (doesn't exist).  Then return what I've typed in the "" marks.

 

** Typcailly IF statements have a 2nd , (comma) for =IF( (Question), Then (Result), Else (other Result)   ).  The 'Else' is always optional though, and not needed here. **