Forum Discussion
Using IF statement with a Filter
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.
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.
8 Replies
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.Hi Anonymous ,
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 , 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.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.
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 QinHi Anonymous and amitchandak thank you so much for your patience.
I have uploaded the sample data with the sample output.
Kind Regards,
Asa
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.Hi Anonymous ,
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
