Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
Could you please help me with query editor formula, bringing out the desired column output (Unique) for below data table.
Ticket Unique
123 1
123 1
645 0
358 0
273 1
987 0
876 0
111 1
111 1
Thanks,
CS
Solved! Go to Solution.
Hi @Anonymous ,
I have created a sample for your reference.
1. Insert an index column in power query.
2. Create a calculated column as below.
Column = var ind = 'Table'[Index]-1
return
IF('Table'[Ticket] = CALCULATE(MAX('Table'[Ticket]),FILTER('Table','Table'[Index] = ind)),0,)
For more details, please check the pbix as attached.
where are you doing this in the front end or in power query?
for a distinct count in DAX on the visual pane just do
unique =
DISTINCTCOUNT ( table[ticket] )
in a new measure
Proud to be a Super User!
Hi Vanessa,
Thanks for your reply!
I had created one in front end with new measure, but that doesn't show up in power query editor columns.
So I am looking at getting this output in power query new column. Because based on this, need to build other logics in power query.
Regards,
CS
what are you trying to do and why do you want to create it there?
you can do it via code in M in power query but its simpler to use the groupby function
see this thread
https://community.powerbi.com/t5/Desktop/count-distinct-on-column-in-power-query/td-p/165494
Proud to be a Super User!
Hi,
Here is the below table which might give you my purpose. I need to arrive at price based on the below conditions.
Vendor-A | 10$ per Ticket |
Vendor-B | 10$ per Unit |
Vendor-C | If qty < 10, then 10$*qty and if qty >10 then 10$ |
Vendor-D | If qty < 10, then 10$ per ticket and if qty >10 then 100 per ticket |
DATA TABLE
Ticket | Vendor | Product | Qty | Unique | Price |
1 | A | KB-1 | 12 | 1 | |
1 | A | KB-2 | 2 | 0 | |
2 | A | KB-3 | 5 | 1 | |
3 | B | MO-1 | 23 | 1 | |
4 | B | KB-2 | 21 | 1 | |
5 | C | AD-1 | 3 | 1 | |
5 | C | AD-2 | 55 | 0 | |
6 | D | FI-1 | 2 | 1 | |
6 | D | KB-2 | 1 | 0 |
Hi @Anonymous ,
I have created a sample for your reference.
1. Insert an index column in power query.
2. Create a calculated column as below.
Column = var ind = 'Table'[Index]-1
return
IF('Table'[Ticket] = CALCULATE(MAX('Table'[Ticket]),FILTER('Table','Table'[Index] = ind)),0,)
For more details, please check the pbix as attached.
What is per ticket, per unit, what is diff for Vendor C <10 and > 10?
You can create a new column using Switch
SWITCH(True(),
[Vendor] ="A",10,
[Vendor] ="B",10
[Vendor] ="C" && [QTY]<10,10,
[Vendor] ="C" && [QTY]>=10,10,
[Vendor] ="D" && [QTY]<10,10,
[Vendor] ="D" && [QTY]>=10,100
)
if you need more help make me @
Appreciate your Kudos.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |