Using Dax Variables to identifying same value in multiple tables/conditions

[lchaplen]

I am trying to identify "house ID" that appear in two separate tables and then a third and only count them if the "house ID" appears in all three tables.  I am using variables and using Intersect but that is not getting me what I need.  Any assistance is helpful

HH_NonCash and OtherIncome & Employ =

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 Energy Assistance")

//need to compbine NonCasha and LIHEAPa

VAR Combined_NonCash_LIHEAP = UNION(NonCasha,LIHEAPa)

VAR Unique_NCandLIHEAP = DISTINCT(Combined_NonCash_LIHEAP)

VAR OI =

CALCULATETABLE(VALUES ('rpt vw_Client_Household_Employment'[Household_Code] ),FILTER('rpt vw_Client_Household_Employment','rpt vw_Client_Household_Employment'[Client_Active] = 1), FILTER('rpt vw_Client_Household_Employment', NOT(ISBLANK('rpt vw_Client_Household_Employment'[HH_Other_Income_Source]))))

VAR Employ =

CALCULATETABLE(VALUES('rpt vw_Client_Households'[Household_Code]),

FILTER('rpt vw_Client_Household_Employment','rpt vw_Client_Household_Employment'[Client_Active] = 1 ),

FILTER('rpt vw_Client_Household_Employment', 'rpt vw_Client_Household_Employment'[Income_Type]= "Wages" || 'rpt vw_Client_Household_Employment'[Income_Type]= "Self-employment"))

// Want to find where the value is the same in these two variables.

VAR CombinedNCandOI = INTERSECT(Unique_NCandLIHEAP,OI)

VAR UniqueNC_OI = DISTINCT(CombinedNCandOI)
//want to find the values where the value is the same from prior to this new variable

Var CombinedNCandOIandEmploy = Intersect (UniqueNC_OI,Employ)

VAR UniqueNCandOIandEmploy = DISTINCT(CombinedNCandOIandEmploy)

RETURN

countrows(UniqueNCandOIandEmploy) // this should be a count of where the household_ID is the same across all 4 variables

[bcdobbs]

I think you can make life easier by using your earlier table variables as filters in subsequent. Something along lines of...

VAR Tbl1 = 

CALCULATETABLE(

VALUES('rpt vw_Client_Households'[Household_Code]),

Your Logic)

VAR Tbl2 =

CALCULATETABLE(

VALUES('rpt vw_Client_Households'[Household_Code]),

Your Logic,

Tbl1)

VAR Tbl3 =

CALCULATETABLE(

VALUES('rpt vw_Client_Households'[Household_Code]),

Your Logic,

Tbl2)

RETURN

COUNTROWS (Tbl3)

[lchaplen]

Ben

Hi I tried to do as you recommended.. I think it will work.. but I'm getting the error: The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

on this:

TEST NCOIEmP =

VAR HH = CALCULATE('rpt vw_Client_Households','rpt vw_Client_Households'[Household_Code],FILTER('rpt vw_Client_Households','rpt vw_Client_Households'[Client_Active]=1))

VAR NonCasha =

CALCULATE('rpt vw_Client_Households','rpt vw_Client_Households'[Household_Code],FILTER('rpt vw_Client_Non_Cash_Benefits','rpt vw_Client_Non_Cash_Benefits'[Client_Active] = 1 ))

VAR LIHEAPa =

CALCULATE('rpt vw_Client_Households','rpt vw_Client_Households'[Household_Code], FILTER('rpt vw_Client_Programs','rpt vw_Client_Programs'[Program] = "Low Income Heating Energy Assistance"))

VAR OI =

CALCULATE('rpt vw_Client_Households','rpt vw_Client_Households'[Household_Code],FILTER('rpt vw_Client_Household_Employment','rpt vw_Client_Household_Employment'[Client_Active] = 1), FILTER('rpt vw_Client_Household_Employment', NOT(ISBLANK('rpt vw_Client_Household_Employment'[HH_Other_Income_Source]))))

VAR Employ =

CALCULATE('rpt vw_Client_Households','rpt vw_Client_Households'[Household_Code],

FILTER('rpt vw_Client_Household_Employment','rpt vw_Client_Household_Employment'[Client_Active] = 1 ),

FILTER('rpt vw_Client_Household_Employment', 'rpt vw_Client_Household_Employment'[Income_Type]= "Wages" || 'rpt vw_Client_Household_Employment'[Income_Type]= "Self-employment"))

RETURN

CALCULATE (

DISTINCT('rpt vw_Client_Households'[Household_Code]),

OI,

Employ,

(NonCasha||LIHEAPa)

)

[bcdobbs]

Hi, 

In your original code you were using CALCULATETABLE on VALUES in order to apply a specific filter context and return a single column of values. I was suggesting you could take each table variable in turn and use it as a filter directly in the next.

In the new code you've sent you're only using CALCULATE which expects an expression that returns a scalar. Instead you're passing it a full table.

[lchaplen]

Ben

Hi well I'm new to BI so could you assist me as I tried and couldn't figure it out I tried this but it seems to duplicate #

Test =

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 Energy Assistance")

VAR Combined_NonCash_LIHEAP = UNION(NonCasha,LIHEAPa)

VAR Unique_NCandLIHEAP = DISTINCT(Combined_NonCash_LIHEAP)

VAR OI =

CALCULATETABLE(

VALUES ('rpt vw_Client_Household_Employment'[Household_Code] ),FILTER('rpt vw_Client_Household_Employment','rpt vw_Client_Household_Employment'[Client_Active] = 1), FILTER('rpt vw_Client_Household_Employment', NOT(ISBLANK('rpt vw_Client_Household_Employment'[HH_Other_Income_Source]))),Unique_NCandLIHEAP)

VAR Employ =

CALCULATETABLE(VALUES('rpt vw_Client_Households'[Household_Code]),

FILTER('rpt vw_Client_Household_Employment','rpt vw_Client_Household_Employment'[Client_Active] = 1 ),

FILTER('rpt vw_Client_Household_Employment', 'rpt vw_Client_Household_Employment'[Income_Type]= "Wages" || 'rpt vw_Client_Household_Employment'[Income_Type]= "Self-employment"),OI)

RETURN

countrows(Employ)

[bcdobbs]

Are you able to send a screen shot of your models relationships?

[lchaplen]

[bcdobbs · Accepted solution]

I wrote this before you sent the diagram. See if it works. If not will study your model and have a think!

Test =

//Household Codes for clients with active non cash benefits.
VAR NonCasha =
	CALCULATETABLE(
		VALUES( 'rpt vw_Client_Households'[Household_Code] ),
		'rpt vw_Client_Non_Cash_Benefits'[Client_Active] = 1 
		)

//Household Codes for clients on Low Income Heating Entery Assistance.
VAR LIHEAPa =
	CALCULATETABLE(
		VALUES( 'rpt vw_Client_Households'[Household_Code] ),
		'rpt vw_Client_Programs'[Program] = "Low Income Heating Energy Assistance"
	)

//Get household codes for clients who either have active non cash benefits or are 
// in low income energy program.
VAR Combined_NonCash_LIHEAP = 
	UNION(NonCasha,LIHEAPa)

VAR Unique_NCandLIHEAP = 
	DISTINCT(Combined_NonCash_LIHEAP)


// Get household codes from employment table that were found above and are active and who do
// have another income source and whose income type is
// wages or self-employment.
// Use TREATAS to switch the above list from 'rpt vw_Client_Households'[Household_Code] to 
// 'rpt vw_Client_Household_Employment'[Household_Code]
VAR Employ =
	CALCULATETABLE(
		VALUES ( 'rpt vw_Client_Household_Employment'[Household_Code] ),
		'rpt vw_Client_Household_Employment'[Client_Active] = 1, 
		NOT ( ISBLANK ( 'rpt vw_Client_Household_Employment'[HH_Other_Income_Source] ) ),
		'rpt vw_Client_Household_Employment'[Income_Type] IN { "Wages", "Self-employment" }
		TREATEAS ( Unique_NCandLIHEAP, 'rpt vw_Client_Household_Employment'[Household_Code] )
	)



RETURN COUNTROWS ( Employ )

[lchaplen]

Ben

Thanks this isn't quite right but it certainly helps me figure out how to do it!  Thanks so much. I will mark as accept. Thanks again!

[bcdobbs]

Is the logic in the first two variables that get union'd together correct. It's effectively doing an "or" wondered if you really wanted "and".

[lchaplen]

HI the first part is an "OR' which is correct.  Then the rest should be comparing the first group to see the Household also has the next two conditions other income and employement income. If the Household has all three then it should be counted distinctly.

[bcdobbs]

Glad it's got you nearer! If you get stuck again let me know; always happy to jump on a call or something.

Looking at your model you're probably making life difficult for yourself as it's still very much in the form of a relational database. Using power query or sql to shape it into a star schema is likely to make your dax much easier. (Have a look at SQL BI Introduction to data modelling for Power BI)

[lchaplen]

Ben

Thanks I was an advance Report writer in Crystal now Biz Objects so on the learning curve with Power BI. The # don't seem right over inflated.. but will help 🙂  Thanks again

[bcdobbs]

More than happy to but I'm on a campsite without a computer so won't be until tomorrow night.

Any chance you can provide either some dummy data or even better a pbix file with some dummy data in the same structure.

[lchaplen]

Ben

I think something isn't working...  # of people with employement income I calculated as 1907.. So the formula to test when I removed employed and the # didn't change total on following 2 formulas 7867

Test NCOIandEMP =

//Household Codes for clients with active non cash benefits.

VAR NonCasha =

    CALCULATETABLE(

        VALUES( 'rpt vw_Client_Households'[Household_Code] ),

        'rpt vw_Client_Non_Cash_Benefits'[Client_Active] = 1

        )

//Household Codes for clients on Low Income Heating Entery Assistance.

VAR LIHEAPa =

    CALCULATETABLE(

        VALUES( 'rpt vw_Client_Households'[Household_Code] ),

        'rpt vw_Client_Programs'[Program] = "Low Income Heating Energy Assistance"

    )

//Get household codes for clients who either have active non cash benefits or are

// in low income energy program.

VAR Combined_NonCash_LIHEAP =

    UNION(NonCasha,LIHEAPa)

VAR Unique_NCandLIHEAP =

    DISTINCT(Combined_NonCash_LIHEAP)

// Get household codes from employment table that were found above and are active and who do

// have another income source and whose income type is

// wages or self-employment.

// Use TREATAS to switch the above list from 'rpt vw_Client_Households'[Household_Code] to

// 'rpt vw_Client_Household_Employment'[Household_Code]

VAR Employ =

    CALCULATETABLE(

        VALUES ( 'rpt vw_Client_Households'[Household_Code] ),

        'rpt vw_Client_Household_Employment'[Client_Active] = 1,

        NOT ( ISBLANK ( 'rpt vw_Client_Household_Employment'[HH_Other_Income_Source] ) ),

        'rpt vw_Client_Household_Employment'[Income_Type] IN {"Wages", "Self-employment"},

'rpt vw_Client_Household_Employment'[Income]<> 0,

        TREATAS (Unique_NCandLIHEAP,'rpt vw_Client_Households'[Household_Code] )

    )

// VAR employUnique = DISTINCT(Employ)

RETURN COUNTROWS (Employ )

------------------------------

Test NCOIandnot EMP =

//Household Codes for clients with active non cash benefits.

VAR NonCasha =

    CALCULATETABLE(

        VALUES( 'rpt vw_Client_Households'[Household_Code] ),

        'rpt vw_Client_Non_Cash_Benefits'[Client_Active] = 1

        )

//Household Codes for clients on Low Income Heating Entery Assistance.

VAR LIHEAPa =

    CALCULATETABLE(

        VALUES( 'rpt vw_Client_Households'[Household_Code] ),

        'rpt vw_Client_Programs'[Program] = "Low Income Heating Energy Assistance"

    )

//Get household codes for clients who either have active non cash benefits or are

// in low income energy program.

VAR Combined_NonCash_LIHEAP =

    UNION(NonCasha,LIHEAPa)

VAR Unique_NCandLIHEAP =

    DISTINCT(Combined_NonCash_LIHEAP)

// Get household codes from employment table that were found above and are active and who do

// have another income source and whose income type is

// wages or self-employment.

// Use TREATAS to switch the above list from 'rpt vw_Client_Households'[Household_Code] to

// 'rpt vw_Client_Household_Employment'[Household_Code]

VAR Employ =

    CALCULATETABLE(

        VALUES ( 'rpt vw_Client_Households'[Household_Code] ),

        'rpt vw_Client_Household_Employment'[Client_Active] = 1,

        NOT ( ISBLANK ( 'rpt vw_Client_Household_Employment'[HH_Other_Income_Source] ) ),

        not('rpt vw_Client_Household_Employment'[Income_Type] IN {"Wages", "Self-employment"}),

'rpt vw_Client_Household_Employment'[Income]<> 0,

        TREATAS (Unique_NCandLIHEAP,'rpt vw_Client_Households'[Household_Code] )

    )

// VAR employUnique = DISTINCT(Employ)

RETURN COUNTROWS (Employ )

[bcdobbs]

Hi,

I'm afraid it's really hard to diagnose this remotely.

I would start by breaking down the query and identify where things are going wrong.

One way is to change what you put in the RETURN so for example return COUNTROWS of each variable in turn.

After that you need to see what is being return in each variable. You could do that with DAX Studio eg:

EVALUATE

CALCULATETABLE(
        VALUES( 'rpt vw_Client_Households'[Household_Code] ),
        'rpt vw_Client_Non_Cash_Benefits'[Client_Active] = 1
        )

or just take each variable in turn and create calcualted tables to identify where the error is happening.

[lchaplen]

Ben

Thanks working on it.. some days Power BI is so easy but then gives a problem where I want to innnerjoin the variables and it is hard vs SQL.  Thanks for your help.. If I find a solution I will let you know.

[lchaplen]

Ben 

Hi unfortunately I can't provide data 😞

[bcdobbs]

No problem. When I'm back on a computer I'll build a demo based on your original code and we'll work it through from there.

[bcdobbs]

What output do you need? Eg how will you use the measure?

[lchaplen]

Counts of unique values that are in all 4 variables.  Example if Houshold A was in all 4 of the variables logic then A would be counted. If houshold B was only in 3 of the 4 variables then I wouldn't want them counted