Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Measures need to replace by Table joins

i have to replace a meaure with table which is like below

 

measure= if (sum(Total)=5, 2.33, if(sum(Total)=6,4.99, if(sum(Total)=9,8.33.......) continues......

i have above set of statements which are hardcoded.......i want to reduce that by using separate table like below

 

Total  Value

5        2.33

6        4.33

9        8.33

 

if i join this table with fact table, how to tackle sum(Total)=5, ?

 

Thanks in advance

Need Help

3 Replies

  • rajulshah's avatar
    rajulshah
    Icon for Resident Rockstar rankResident Rockstar
    3 years ago

    Anonymous 

    You can use the following DAX measure:

    Measure =
VAR SelectedTotalValue =
    SELECTEDVALUE ( Table[Total] )
VAR TotalValue =
    SUM ( Table1[Total] )
RETURN
    CALCULATE (
        MAX ( Table[Value] ),
        FILTER ( Table, SelectedTotalValue = TotalValue )
    )

     

    Here, Table is the one that you have created and Table1 is the table from which you are calculating the sum.

     

    Please let me know if this didn't work.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    No it is not working

    i created another table named as Test which has columns Total, Value...i joined both fact and text on Total....i'm able to see values....

     

    My query is Measure consisting SUM, how we can tackle that

    • rajulshah's avatar
      rajulshah
      Icon for Resident Rockstar rankResident Rockstar
      3 years ago

      Anonymous I am still not able to understand the issue. Can you please elaborate?