Forum Discussion

lchaplen's avatar
lchaplen
Icon for Helper II rankHelper II
4 years ago
Solved

Calculating unique count across two tables values each with conditions.

I have two tables Programs and Benefits.  There are people I need to count uniquely based on conditions in each table Programs:   person is active = 1 and Program = XYZ and startdate = MM/DD/YYY Be...
Need Help
  • lchaplen's avatar
    lchaplen
    4 years ago

    HI so I cracked it.. one thing to mention in Variables you can't go off another variable.

    TEST HH_Non-Cash_Benefit_w_LIHEAP =
    VAR NonCasha =
    CALCULATETABLE(VALUES('rpt vw_Client_Households'[Household_Code]),FILTER('rpt vw_Client_Non_Cash_Benefits','rpt vw_Client_Non_Cash_Benefits'[Client_Active] = 1 ))
    VAR LIHEAPa =
    CALCULATETABLE(VALUES('rpt vw_Client_Households'[Household_Code]),'rpt vw_Client_Programs'[Program] = "Low Income Heating Assistance")
    VAR Combined_NonCash_LIHEAP = UNION(NonCasha,LIHEAPa)
    VAR Unique_NCandLIHEAP = DISTINCT(Combined_NonCash_LIHEAP)
    RETURN
    Countrows(Unique_NCandLIHEAP)
lchaplen's avatar
lchaplen
Icon for Helper II rankHelper II
4 years ago

Well it is getting me closer.. this is my formula.. Note the Person code is derived from a 3rd table. I'm getting the error:  "A function 'PLACEHOLDER' has been used in a TRUE/FALSE expression that is used as a table filter expresssion.  This is not allowed" 

 

TEST HH_Non-Cash_Benefit_w_LIHEAP =
VAR NonCash =
CALCULATETABLE(VALUES('rpt vw_Client_Households'[Household_Code]),not(isblank('rpt vw_Client_Household_Employment'[HH Non_Cash])))
VAR LIHEAP =
CALCULATETABLE(VALUES('rpt vw_Client_Households'[Household_Code]),'rpt vw_Client_Programs'[Program] = "Low Income Heating Assistance")
VAR Combined_NonCash_LIHEAP = UNION(NonCash,LIHEAP)
VAR Unique_NCandLIHEAP = DISTINCT(Combined_NonCash_LIHEAP)
RETURN
COUNTROWS(Unique_NCandLIHEAP)
lchaplen's avatar
lchaplen
Icon for Helper II rankHelper II
4 years ago

HI so I cracked it.. one thing to mention in Variables you can't go off another variable.

TEST HH_Non-Cash_Benefit_w_LIHEAP =
VAR NonCasha =
CALCULATETABLE(VALUES('rpt vw_Client_Households'[Household_Code]),FILTER('rpt vw_Client_Non_Cash_Benefits','rpt vw_Client_Non_Cash_Benefits'[Client_Active] = 1 ))
VAR LIHEAPa =
CALCULATETABLE(VALUES('rpt vw_Client_Households'[Household_Code]),'rpt vw_Client_Programs'[Program] = "Low Income Heating Assistance")
VAR Combined_NonCash_LIHEAP = UNION(NonCasha,LIHEAPa)
VAR Unique_NCandLIHEAP = DISTINCT(Combined_NonCash_LIHEAP)
RETURN
Countrows(Unique_NCandLIHEAP)