Treatas with condition

[Nes1205]

Hello everyone,

Basically my question is:

- can you use treatas based on a condition (if the condition is true, use treatas, if the condition is false, don't use treatas).

- if not, can the first argument of the treatas be different based on a condition (if the condition is true, one set of values, if the condition is false, another set of values). 

This is the situation:
I have a dataset with respondent data and a target table that I need to use to filter the respondents.

I want to create a virtual relationship in a dax measure like this:

Count respondents = 
VAR AgeAgeValues = VALUES(Age[Age])
VAR AgeTargetValues = VALUES('TargetGroups Age'[Age])
VAR GenderValues = VALUES('TargetGroups Gender'[gender_code])

RETURN
CALCULATE(
    DISTINCTCOUNT('respondents'[ID]),
    TREATAS(AgeTargetValues, Age[Age]),
    TREATAS(GenderValues, Gender[gender_code])
)

Problem is, that for example if my target is "all women", that AgeTargetValues will be empty. In that case the treatas on age shouldn't be applied (if possible) or should use AgeAgeValues.

The same goes for gender and about 12 other attributes linked to a respondent.
I can make it work with "FILTER" but this is less performant and it is a large and complex dataset...

Does anyone have a solution? I tried with "IF"

VAR AgeValues = IF(COUNTROWS(AgeTargetValues), AgeTargetValues, AgeAgeValues)

but it seems that doesn't return a table because it gives this error:
The TREATAS function expects a table expression for argument '', but a string or numeric expression was used.

Thank you!

Vanessa

[some_bih]

Hi @Nes1205 I have some questions

Part for relatioship

I can't create a physical relationship between

• targetgroup age --> age
• targetgroup gender --> gender

Q1: In table TargetGroups Gender there are duplicate values in column Gender_code, see pict below?

Is this ok

Q2: if you need for model "create" relationship to "connect" data on row level in some table based on another table input, which two tables and columns should be connected? If yes, please provide example and expected outuput

Q3: what is your general request? what answers you want to provide for users, beside using TREATAS?

Maybe that answer could be provided with another solutions....

[Nes1205]

Hi @some_bih ,

Q1: yes, duplicate values for gender are normal in TargetGroups Gender. The TargetId needs to be unique, but in this example both target "Women" and "Woman 25-45" need to be filtered on gender "Women". In the table Gender, the genders are unique.

Q2: relationships would be: 
- Age[Age] (one) to 'TargetGroups Age'[Age] (many), crossfilter "both"

- Gender[Gender_code] (one) to 'TargetGroups Gender'[Gender_code] (many), crossfilter "both"

(and this also for other criteria, not added in this model)

Q3: What I want is the best possible solution to be able to select a certain target group and that in my measures respondents are filtered on the criteria in that target group.

Thanks for taking the time!

Vanessa

[some_bih]

Hi @Nes1205 just info: your file is updated to newer version of pbi compared to my installation, so currently I have issues opening your file. I should resolve this issue before end of this week.

For Q3 you want some statistics based on target group, like distinct of respon.?

Did you try already to get target group data into fact table on row level to provide detailed data?

If not, what would be data from some table you would like to see in fact table?

[Nes1205]

There are many different calculations I need, on which I can apply different slicers (not only to filter on respondents). I have all the calculations I need in the dataset, it all works, but it would be a lot more user fiendly if the users of the reports could select a set of respondents with 1 slicer on target group.

There is already a fact table based on the respondents and their answers, this has more then 10 million rows. 

Since one respondent can be part of more than 1 target group, I think adding the target group to the fact table will explode the number of rows ( there are 150.000 respondents and more than 100 target groups).

[some_bih]

Hi @Nes1205 

Part below and questions

Since one respondent can be part of more than 1 target group, I think adding the target group to the fact table will explode the number of rows ( there are 150.000 respondents and more than 100 target groups).

Q: there is logic per respondents to belong some target group? 

[Nes1205]

Yes, a targetgroup is a grouping of respondents.

These are 3 examples of respondents

These are 3 examples of TargetGroups

So target 1 25-54 refers to respondent 131 and 101 because their age is between 25-54.

Target 2 Women 25-54 refers to respondent 131, because she is a woman and her age is between 25-54.
Target 3 Women refers to respondent 2 and 131  because they are women.

[some_bih]

Hi @Nes1205 it is hard to provide possible solution without model and details.

Still, try v2 below

Count respondents V2 =
VAR AgeAgeValues = VALUES(Age[Age])
VAR AgeTargetValues = VALUES('TargetGroups Age'[Age])
VAR GenderValues = VALUES('TargetGroups Gender'[gender_code])

RETURN
CALCULATE(
DISTINCTCOUNT('respondents'[ID]),
TREATAS(VALUES('TargetGroups Age'[Age]), Age[Age]),
TREATAS(VALUES('TargetGroups Gender'[gender_code]), Gender[gender_code])
)

[Nes1205]

Thanks for taking the time to respond!
But I don't really see the difference in your code and my code, I think?

this is my code:

CALCULATE(
    DISTINCTCOUNT('respondents'[ID]),
    TREATAS(AgeTargetValues, Age[Age]), 
    //AgeTargetValues = VALUES('TargetGroups Age'[Age])
    TREATAS(GenderValues, Gender[gender_code])
)

This is yours:

CALCULATE(
   DISTINCTCOUNT('respondents'[ID]),
   TREATAS(VALUES('TargetGroups Age'[Age]), Age[Age]), 
   //VALUES('TargetGroups Age'[Age]) is the same as my "AgeTargetValues"
   TREATAS(VALUES('TargetGroups Gender'[gender_code]), Gender[gender_code])
)

My model is to big to post, I could create a basic one to simulate the issue. But I don't think the model really matters here.

The question is:

- can you use treatas based on a condition (if the condition is true, use treatas, if the condition is false, don't use treatas).

- if not, can the first argument of the treatas be different based on a condition (if the condition is true, one set of values, if the condition is false, another set of values). 

Sorry if my question was not clear...

KInd regards,

Vanessa

[some_bih]

Hi Vanessa,

OK for model.

According to error you provided, 

The TREATAS function expects a table expression for argument '', but a string or numeric expression was used.

VALUES function is table manipulator, related link for VALUES

please check link for TREATAS function details.

I guess you did not try v2?

[Nes1205]

I created a sample dataset.

What I want, is when a targetgroup is selected, that the respondents table is filtered according to the selected target group.

I have 3 examples:

• Targetgroup Women 25-45, 1 page with slicers on age and gender, 1 page with slicer on targetgroup.
• Targetgroup 25-45, 1 page with slicers on age and gender, 1 page with slicer on targetgroup.
• Targetgroup Women, 1 page with slicers on age and gender, 1 page with slicer on targetgroup.

I can't create a physical relationship between

• targetgroup age --> age
• targetgroup gender --> gender

because I would need to set the crossfilter to "both", creating ambiguous relationships.

I can make it work with the FILTER function (measure: Count respondents with filter) but the TREATAS function is better for performance so if possible, I would like to achieve the same with TREATAS

measures Count respondents and Count respondents V2

• give a correct result for target Women 25-45 because there are values for this target in both targetgroup age and targetgroup gender table. (sheet "With TargetGroups: Women 25-45")
• give no result for target 25-45 because there are no values for this target in targetgroup gender table. (sheet "With TargetGroups: 25-45")
• give no result for target Women because there are no values for this target in targetgroup age table. (sheet "With TargetGroups: All Women")
  

If I could work with a condition to determin the first argument for the TREATAS function, that would resolve my issue. I tried it with an IF function (measure Count respondents with IF) but the result of the IF function can't be used as an argument in the TREATAS function, because it is not a table.

Example PBIX Targets 

Many thanks!

Vanessa

[some_bih]

Hi @Nes1205 I will take a look and let you know what could be done