Using IF statement with a Filter

[Asa94]

Hi all,

I hope you are well,

I am doing an analyis which looks at Part Numbers being ordered on 2 order types (ZEO and ZSO) in the same month. Like the image below: 

I have a measure ZEO Test which looks to see which part numbers have been ordered simultaneously on both ZEO and ZSO in the same month. If a part number has been ordered on both order types in the same month, it's supposed to say "see". I think the issue is that I would need to add a FILTER in the measure which looks at the month.

ZEO Test = IF('Kaizen EO'[ZEO Count] > 0 && 'Kaizen EO'[ZSO Count] > 0,"see","seen")

If anyone can assist, that would be greatly appreciated 🙂

Regards,

Asa

[v-eqin-msft]

Hi @Asa94 ,

If you could transform the table by pivoting:

please try:

Measure = IF( COUNT(Pivot[ZEO])>0 && COUNT('Pivot'[ZSO])>0,"see","seen") 

Output:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Asa94]

Hi @v-eqin-msft ,

Sorry for the delayed reply. I have been getting errors on my Pivot, hence my delay.

But I did find the issue whereby the Order Qty column was not changed from General to Number. Which in turn didn't give me the option to Aggregrate as a sum. 

I sorted it out and it works! 

Thank you so much for your help. I really do appreciate it 🙂

Kind Regards,

Asa

[v-eqin-msft · Accepted solution]

Hi @Asa94 ,

Is your expected output like this:

ZEO = CALCULATE(COUNTROWS('Table'),'Table'[SAP Ord Type]="ZEO") 

ZSO = CALCULATE(COUNTROWS('Table'),'Table'[SAP Ord Type]="ZSO") 

ZEO Test = IF([ZEO] > 0 && [ZSO] > 0,"see","seen")

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Asa94]

Hi @v-eqin-msft ,

Yes, this is the expected output. Thank you so much. I appreciate it. 

I want to ask, you wouldn't recommend that I pivot the Order Qty column with the Order Type column in the query? I'm guessing as @amitchandak has explained, the ZEO Test measure will not work.

Kind Regards,

Asa

[Asa94]

Hi @v-eqin-msft and @amitchandak thank you so much for your patience.

I have uploaded the sample data with the sample output.

https://we.tl/t-MfczkVSEC1 

Kind Regards,

Asa

[v-eqin-msft]

Hi @Asa94 ,

Any updates about your sample?

You may kindly take a look at the blogs to know

How to Get Your Question Answered Quickly - Microsoft Power BI Community

How to provide sample data in the Power BI Forum - Microsoft Power BI Community

Best Regards,
Eyelyn Qin

[amitchandak]

@Asa94 , Make sure ZEO Count and ZSO Count are measure and ZEO Test is also a measure

ZEO Test = IF([ZEO Count] > 0 && [ZSO Count] > 0,"see","seen")

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Asa94]

Hi @amitchandak, I see, ZEO and ZSO Count are summarized from another table. 

In the meantime, I'm building the sample data and output to share with you.