Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
powerbiss
Helper I
Helper I

Sum Distinct values based on string values

Hi Team,
I am trying to sum distinct values from amount_total column based on state and invoice_status columns. State should be "draft" and invoice_status should be "no"

Answer: 200000

 

Thanks for all your helpsum_values_strings.png

 

1 ACCEPTED SOLUTION
johnt75
Super User
Super User

create a measure like

Sum of distinct values =
var distinctAmounts = CALCULATETABLE(VALUES('Table'[amount_total]), 
TREATAS( { ( "draft", "no") }, 'Table'[state], 'Table'[invoice_status])
)
return SUMX(distinctAmount, [amount_total])

View solution in original post

3 REPLIES 3
powerbiss
Helper I
Helper I

Hi John,

Really appreciate your help in solving my problem. What is TREATAS.

Your solution worked great.

 

Thanks again

TREATAS is a means of moving or creating filters. If you want to filter a number of columns by different values then you can create a table using {} table constructor and then use TREATAS to say this column of this table matches the first value, this column of that table matches the 2nd value etc.

Also handy if you want to move an existing filter from one table to another, e.g. TREATAS(VALUES('Table1'[id]), 'Table2'[id])

johnt75
Super User
Super User

create a measure like

Sum of distinct values =
var distinctAmounts = CALCULATETABLE(VALUES('Table'[amount_total]), 
TREATAS( { ( "draft", "no") }, 'Table'[state], 'Table'[invoice_status])
)
return SUMX(distinctAmount, [amount_total])

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.