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

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...

Need Help

bcdobbs

4 years ago

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 )

bcdobbs
Community Champion
4 years ago
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
  Helper II
  4 years ago
  Ben
  Hi unfortunately I can't provide data 😞
  - bcdobbs
    Community Champion
    4 years ago
    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.
- lchaplen
  Helper II
  4 years ago
  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
    Community Champion
    4 years ago
    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.

bcdobbs

Community Champion

4 years ago

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

lchaplen

Helper II

4 years ago

bcdobbs

Community Champion

4 years ago

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 )

Forum Discussion

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

Related content

Identifying duplicates

Conditional Formatting with Matrix Table

Rolling Date Identifiers

Count distinct values with condition in a table variable

Identifying Stock On Hand