Forum Discussion

Kella's avatar
Kella
Frequent Visitor
3 years ago
Solved

DAX Measure - To get only one value for multiple rows

I am new to Power BI, I am facing issue where I want to create a new column based on the Invoice Date and the Invoice No.
In below table, I want to calculate "What I want" column using DAX​ measure to get $35 for each Invoice No and displaying values ($35) for any rows.

Invoice No   Invoice Date SKU   Invoice    What I want
1001    7/12/2022 a123  $250    $35
1001    7/12/2022 b123  $100 
1001    7/12/2022 c123  $10 
1002    6/6/2023 z123  $2    $35
1002    6/6/2023 y123  $4 
1023    1/4/2023 k123  $1    $35
1023    1/4/2023 v123  $1,00 
1023    1/4/2023 s123  $5 
Need Help
  • Ahmedx's avatar
    Ahmedx
    3 years ago

    pls try this

    sum Invoice = SUM('Table'[Invoice])


-----
35 in Invoice = 
 
VAR _t1 = SELECTEDVALUE('Table'[Invoice No])
VAR _t2 = SELECTEDVALUE('Table'[Invoice Date])
VAR _tbl = FILTER(ALL('Table'),'Table'[Invoice No]=_t1&&'Table'[Invoice Date]=_t2)
VAR Results= RANKX(_tbl,[sum Invoice],,DESC)
RETURN
IF(Results =1,35)

     

     

5 Replies

  • Ritaf1983's avatar
    Ritaf1983
    Super User
    3 years ago

    Hi Kella 

    If i understood you correctly just add column with value you need an format it as currency:

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

    • Kella's avatar
      Kella
      Frequent Visitor
      3 years ago

      Thanks for your time but I need $35 to display for each Invoice No not for each row.

  • Ahmedx's avatar
    Ahmedx
    Super User
    3 years ago

    pls try this

    Column = 
VAR _t1 = [Invoice No]
VAR _t2 = [Invoice Date]
VAR _tbl = FILTER(ALL('Table'),[Invoice No]=_t1&&[Invoice Date]=_t2)
VAR Results= RANKX(_tbl,[Invoice],,DESC)
RETURN
IF(Results =1,35)

    • Kella's avatar
      Kella
      Frequent Visitor
      3 years ago

      Thanks, @Ahmedx for your time. I think DAX you share is for the calculated column. I'm looking for a DAX measure to manage this.

  • Ahmedx's avatar
    Ahmedx
    Super User
    3 years ago

    pls try this

    sum Invoice = SUM('Table'[Invoice])


-----
35 in Invoice = 
 
VAR _t1 = SELECTEDVALUE('Table'[Invoice No])
VAR _t2 = SELECTEDVALUE('Table'[Invoice Date])
VAR _tbl = FILTER(ALL('Table'),'Table'[Invoice No]=_t1&&'Table'[Invoice Date]=_t2)
VAR Results= RANKX(_tbl,[sum Invoice],,DESC)
RETURN
IF(Results =1,35)