Forum Discussion

Dudeman123's avatar
Dudeman123
New Member
1 year ago
Solved

Data table in variable

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 hol...
  • Anonymous's avatar
    Anonymous
    1 year ago

    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's avatar
bhanu_gautam
Super User
1 year ago

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]))