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
LSP0weR31
Helper I
Helper I

Custom Column query if and distinctcount then " " else " "

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!

1 ACCEPTED 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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

17 REPLIES 17
AndresSalomon
Helper II
Helper II

Hi, did you try creating the measure instead of a column? It Is a good practice to create measures instead of columns, always it Is possible.

No, I hadn't thought of a measure. Can you suggest a DAX formula for the measure?

 

@LSP0weR31

 

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:

 

1.PNG

 

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:

 

2.PNG

 

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?

 

Capture.JPG

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))


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur

 

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))


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

 

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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 YearColumn…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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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,

 

 

@LSP0weR31

 

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?

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.