Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
AREO-07
Regular Visitor

using IF statement as calculated column and use this as filter

Hi,

 

I really need assistance in solving a problem in power bi.

 

I want to create a calculated column using if statement. I have a table with customer ids and a monthly report from 2019 to present.

I want to identify which unique customer ids have transactions in each monthly report.

 

Report DateLoyalty Card NumberLitersResult
Nov-19AC52897420 
Apr-19AC5289749 
May-19AC52897433 
Dec-19AA06211310 
May-19AA6797809 
Nov-19AB33316410TRUE
Sep-19AB3331642TRUE
May-19AB3331642TRUE
Jun-20AB3331642TRUE
Jun-20AB3331642TRUE
Dec-19AB3331643TRUE
Apr-19AC8132029 
Mar-20AB5703815TRUE
Mar-20AB5703813TRUE
May-20AB33316422 
Jul-20AB33316420 
Jul-20AC62715010 
Aug-20AB3331643 
Aug-20AB3331644TRUE
Aug-20AB3331648TRUE
Aug-20AB3331645TRUE
Sep-20AB3331645TRUE
Sep-20AB3331647TRUE
Jul-20AB3331647TRUE
Jul-20AB57038127TRUE
Aug-20AB57038114TRUE

 

 I want to add the value "True" for the customer ids that will pass the condition same with result on the table above.

 

Please confirm if this is possible or there are other ways to create this result, perhaps in power query?

 

Really hoping someone can help me.

 

Thanks in advance.

6 REPLIES 6
wdx223_Daniel
Super User
Super User

CountRows(Filter(all(MonthlyReport[ID],MonthlyReport[Date]),Format(MonthlyReport[Date],"yyyymm")=Format(Customer[Date],"yyyymm")&&MonthlyReport[ID]=Customer[ID]))>0

AlB
Super User
Super User

Hi@AREO-07 

Do you mean they have to have data in all the dates present in the table. If so, none of the IDs shown complies.

1. Place Loyalty Card in a table visual

2. Create this measure and  place it in the visual

 

Measure =
VAR allDates_ =
    ALL ( Table1[Report Date] )
VAR currentIDDates_ =
    DISTINCT ( Table1[Report Date] )
VAR missingDates_ =
    COUNTROWS ( EXCEPT ( allDates_, currentIDDates_ ) )
RETURN
    IF ( missingDates_ = 0, TRUE (), FALSE () )

 

It would be better to have an additional table with the dates so that you can use that as reference for the period in which you want the cards to be active

This can also be implemented in Power Query

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

AREO-07
Regular Visitor

@AlB 

 

I have created a dax measure but i am having issues with the calculate function what could be the proper workaround here 

 

AREO-07_0-1604994033965.png

 

Hoping for your response.

 

Thanks

AREO-07
Regular Visitor

Maybe my logic is incorrect.

 

But what i need is to identify which card numbers have transactions every monthly report and group them so that i could create a graph that shows how frequent they are transacting each month.

 

See my table below.

 

AREO-07_0-1604394389849.png

 

Fowmy
Super User
Super User

@AREO-07 

Not clear enough, could you explain more on how the logic should be applied to mark as TRUE

 

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

I want to mark "True" if that unique card number shows in every monthly report.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors