Data table in variable

[Dudeman123]

Hi i have a query 

So I have 5 columns called objective 1, objective 2,... Objective 5. Which holds values yes or no.

I have 3 other columns called category A, category B, category C.

Now this holds data on applications

The goal is to count the number of objectives which has value "No".

So if category A is "Yes" then I have to count the number of no's from objective 1,2,4

If category B is "Yes" then I have to count number of no's from objective 1,2,5

And category C it's 1,5.

Issue is an application can have multiple categories so I don't want to duplicate the count.

So if it's category A and B is Yes then I should count 1,2,4,5 no of "No".

I tried using data table where I store each objective number as a variable for each category and then perform union and distinct. But it's throwing me an error.

[Anonymous · Accepted solution]

Hi @Dudeman123 ,

Thanks for bhanu_gautam's reply!

And @Dudeman123 , here may be a simpler DAX. Here is my sample data:

Then use this DAX to create a measure:

Count No = 
SUMX(
    Applications,
    VAR CatA = Applications[Category A] = "Yes"
    VAR CatB = Applications[Category B] = "Yes"
    VAR CatC = Applications[Category C] = "Yes"
    RETURN
    IF( CatA || CatB || CatC, IF( Applications[Objective 1] = "No", 1, 0 ) ) +
    IF( CatA || CatB, IF( Applications[Objective 2] = "No", 1, 0 ) ) +
    IF( CatA, IF( Applications[Objective 4] = "No", 1, 0 ) ) +
    IF( CatB || CatC, IF( Applications[Objective 5] = "No", 1, 0 ) )
)

And the final output is as below:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bhanu_gautam]

@Dudeman123 Create a calculated column for each category to identify the relevant objectives:

DAX
CategoryA_Objectives =
IF([Category A] = "Yes",
IF([Objective 1] = "No", 1, 0) +
IF([Objective 2] = "No", 1, 0) +
IF([Objective 4] = "No", 1, 0),
0
)

CategoryB_Objectives =
IF([Category B] = "Yes",
IF([Objective 1] = "No", 1, 0) +
IF([Objective 2] = "No", 1, 0) +
IF([Objective 5] = "No", 1, 0),
0
)

CategoryC_Objectives =
IF([Category C] = "Yes",
IF([Objective 1] = "No", 1, 0) +
IF([Objective 5] = "No", 1, 0),
0
)

Create a measure to count the distinct "No" values across the objectives for each application:

DAX
DistinctNoCount =
VAR ObjectivesA =
IF([Category A] = "Yes",
UNION(
SELECTCOLUMNS(FILTER(ALL('Table'), [Objective 1] = "No"), "Objective", 1),
SELECTCOLUMNS(FILTER(ALL('Table'), [Objective 2] = "No"), "Objective", 2),
SELECTCOLUMNS(FILTER(ALL('Table'), [Objective 4] = "No"), "Objective", 4)
),
BLANK()
)

VAR ObjectivesB =
IF([Category B] = "Yes",
UNION(
SELECTCOLUMNS(FILTER(ALL('Table'), [Objective 1] = "No"), "Objective", 1),
SELECTCOLUMNS(FILTER(ALL('Table'), [Objective 2] = "No"), "Objective", 2),
SELECTCOLUMNS(FILTER(ALL('Table'), [Objective 5] = "No"), "Objective", 5)
),
BLANK()
)

VAR ObjectivesC =
IF([Category C] = "Yes",
UNION(
SELECTCOLUMNS(FILTER(ALL('Table'), [Objective 1] = "No"), "Objective", 1),
SELECTCOLUMNS(FILTER(ALL('Table'), [Objective 5] = "No"), "Objective", 5)
),
BLANK()
)

VAR AllObjectives =
UNION(
COALESCE(ObjectivesA, SELECTCOLUMNS({}, "Objective", BLANK())),
COALESCE(ObjectivesB, SELECTCOLUMNS({}, "Objective", BLANK())),
COALESCE(ObjectivesC, SELECTCOLUMNS({}, "Objective", BLANK()))
)

RETURN
COUNTROWS(SUMMARIZE(AllObjectives, [Objective]))