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.
Hi there,
I'm trying to add a column in Query Editor with the following but it returns errors, how can I distinctcount in query editor to return a specific value in the custom column?
if [Vendor Code] = [Vendor Code] and [YearMonth] = [YearMonth] and distinctcount([#"PO + Vendor Code"]) > 8 then "8+" else "no"
I want to use this custom column to create a stacked bar chart by month that counts only Vendor codes that has more than 8 POs in that month, if that helps understanding what I'm trying to achieve.
Thank you!
Solved! Go to Solution.
Hi,
Try this calculated column formula
=if(CALCULATE(DISTINCTCOUNT(Data1[PO no]),FILTER(Data1,Data1[Vendor Code]=EARLIER(Data1[Vendor Code])&&Data1[Month Year]=EARLIER(Data1[Month Year])))>8,">8","<=8")
Hope this helps.
No, I hadn't thought of a measure. Can you suggest a DAX formula for the measure?
To achieve your requirement, using DAX expression will be easier that using Power Query script.
Since you didn't share us your source table structure and sample data. I have assumed it is something like this:
Create a calculated column with DAX expression like:
Flag = IF ( CALCULATE ( DISTINCTCOUNT ( Table1[PO] ), FILTER ( Table1, Table1[Vender] = EARLIER ( Table1[Vender] ) ) ) >= 8, "8+", "no" )
The result shows like:
Thanks,
Xi Jin.
Hi @LSP0weR31 and @v-xjiin-msft-
As @v-xjiin-msft said, we don't have your table structure so the result maybe won't be the expected one. If you can, please share a sample data table.
"I want to use this custom column to create a stacked bar chart by month that counts only Vendor codes that has more than 8 POs in that month, if that helps understanding what I'm trying to achieve."
As you need to count only those Vendor codes that have more that 8 POs, I will try something like this:
[MeasureName]= COUNTX( FILTER(tblData, tblData[POs] >= 8), tblData[VendorCode] )
Then use the Month column in the X axis and VendorCode column in the Y axis of your visualization and it will filter automatically (the measure in the Values area, of course).
I'm imagine a table like this:
VendorCode_____Month_____POs
1__________________Sep________1
1__________________Jan________8
2__________________Dec________9
...
Please let me know if this helps. Thanks!
Kind regards,
Thank you both for helping me with this.
The table looks like this:
PO no Vendor Code Month Year
12345 100748 Nov 2017
12345 100748 Nov 2017
11223 100748 Nov 2017
11223 100748 Nov2017
12645 100748 Nov2017
11133 100748 Nov2017
12645 11234 Nov 2017
12645 11234 Nov 2017
11564 98765 Oct 2017
12222 100748 Nov2017
12233 100748 Nov2017
13322 100748 Nov2017
13452 100748 Nov2017
15555 100748 Dec2017
I then want to show number of distinct vendors that has more than 8 PO no per month as per below. I'm thinking I could use the measure to filter the graph?
I get an error message for the below syntax? I'm not sure I understand the EARLIER command?
Number POs per Vendor Month = IF(CALCULATE(DISTINCTCOUNT(Table1[PO no), FILTER(Table1, Table1[Vendor Code] = EARLIER(Table1[Vendor Code]) AND FILTER(Table1, Table1[Month Year] = EARLIER(Table1[Month Year]) ))) >= 8, "8+", "No")
Thanks,
Lisa
Hi,
Try this measure and drag this to your visual
=COUNTROWS(FILTER(SUMMARIZE(Table1[Vendor Code],[Vendor Code],"ABCD",DISTINCTCOUNT(Table1[PO no])),[ABCD]>8))
It give me this error message: A single value for column 'Vendor Code' in table 'Table1' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.
Any ideas?
Hi,
Sorry for the error there. Try this
=COUNTROWS(FILTER(SUMMARIZE(VALUES(Table1[Vendor Code]),[Vendor Code],"ABCD",DISTINCTCOUNT(Table1[PO no])),[ABCD]>8))
Thanks @Ashish_Mathur for helping me with this.
I need to filter by 2 columns and return a value of either >8 or <8 in the column so that I can use that as a filter in the visual.
Vendor 8+ POs = COUNTROWS(FILTER(SUMMARIZE(VALUES(SAP_Export[Vendor Code]),[Vendor Code] "&" SUMMARIZE(VALUES('Date'[Month-Year]),[Month-Year],"ABCD",DISTINCTCOUNT(SAP_Export[Purchasing Document])),[ABCD]>8)))
I used your suggestion which returned a number '992' in every row of the column. I tried to add the second requirement in your suggestion but that gives me another error message..
What I am after is a column that returns a value of either >8 or <8 on each row.
Distinctount Purchase order numbers (Column A) IF in the same month (Column B)and IF the same vendor (Column C) .
I then want to count the number of Vendors each month with more than 8 Purchase orders to them and present that number over time in a stacked colun chart.
It has been suggested that a custom column can be used as a filter for the visual, not sure if this is the best way to go about it though.
Thanks for your time, it is so appreciated as I know we are all short of it 🙂
Hi,
My formula is a measure (not a calculated column). In a simple table (which i can paste into Excel), share your data and the expected result.
Thanks @Ashish_Mathur
Data table looks similar to below, that is the same PO number can be on many rows as can the Vendor code.
PO no | Vendor Code | Month Year | Column… | Column… |
12348 | 11234 | Sep-17 | ||
12345 | 100748 | Nov-17 | ||
12346 | 11234 | Sep-17 | ||
12345 | 100748 | Nov-17 | ||
12346 | 11234 | Sep-17 | ||
11223 | 100748 | Nov-17 | ||
12345 | 100748 | Nov-17 | ||
11223 | 100748 | Nov-17 | ||
12349 | 11234 | Nov-17 | ||
12645 | 100748 | Nov-17 | ||
12647 | 100748 | Oct-17 | ||
11133 | 100748 | Nov-17 | ||
12646 | 98765 | Oct-17 | ||
12645 | 11234 | Nov-17 | ||
12648 | 100748 | Oct-17 | ||
12645 | 11234 | Nov-17 | ||
12649 | 98765 | Oct-17 | ||
11564 | 98765 | Oct-17 | ||
12650 | 11234 | Oct-17 | ||
12222 | 100748 | Nov-17 | ||
12651 | 98765 | Oct-17 | ||
12233 | 100748 | Nov-17 | ||
12652 | 11234 | Oct-17 | ||
13322 | 100748 | Nov-17 | ||
12653 | 98765 | Oct-17 | ||
13452 | 100748 | Nov-17 | ||
12654 | 11234 | Dec-17 | ||
15555 | 100748 | Dec-17 | ||
… | … | … |
I want to show in a stacked column chart - distinct count of vendor codes that has more than 8 POs in the month.
Hi,
Try this calculated column formula
=if(CALCULATE(DISTINCTCOUNT(Data1[PO no]),FILTER(Data1,Data1[Vendor Code]=EARLIER(Data1[Vendor Code])&&Data1[Month Year]=EARLIER(Data1[Month Year])))>8,">8","<=8")
Hope this helps.
Thank you, thank you!
This worked perfectly 🙂 And I've learned so much along the way.
Really appreciate your help with this @Ashish_Mathur
You are welcome.
Thanks so much for assisting me @v-xjiin-msft and @AndresSalomon,
If you have a minute, can you have a look at the below and see if what I'm looking to visualise is possible with this measure? I get an error message for the syntax but I don't really understand the EARLIER function so not sure how to correct.
Thanks again,
Earlier() function is used in calculated column not measure. So in your scenario, change the measure to calculated column can resolve your issue.
Thanks,
Xi Jin.
Thanks @v-xjiin-msft
I vhanged to calculated column, now I get an error message saying the DISTINCTOUNT function only accepts a column reference as an argument.
Column = IF(CALCULATE(DISTINCTCOUNT(Table1[PO no), FILTER(Table1, Table1[Vendor Code] = EARLIER(Table1[Vendor Code]) AND FILTER(Table1, Table1[Month Year] = EARLIER(Table1[Month Year]) ))) >= 8, "8+", "No")
Any suggestions?
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 |
---|---|
112 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |