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

2 Replies

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.