Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

different dax query for each row

I have two tables    table 1    id  a   b    c    d  (columns) 1   0   1    1    1  (rows) 2   1   0    1    0     table 2   feature  adoption  frequency (columns) a b c d   Now i wan...
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    Hi Anonymous,

    I try to reproduce yourscenario and get expected result using the following steps.

    1. Right click Table1 -> Edit Query, like the screenshot.



    2. Select a,b,c,d column->click Unpivot columns->Close&Apply. You will get the table like the second screenshot below.





    3. Create a relationship between Table1 and Table2. More details about creating and management relationship, please review this article.



    4. Create a calculated column using the formula, you will get the desired result.

    adoption = CALCULATE(COUNT(Table1[Value]),Table1[Value]=1)



    If you have other issues, don't hesitate to let me know.

    Best Regards,
    Angelia

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
9 years ago

That's pretty nasty. Off the top of my head, maybe a custom column in table 2, something like:

 

IF([Feature] = "a",SUM('table 1'[a]),IF([Feature] = "b",SUM('table 1[b],...

Probably cleaner with a SWITCH statement.