using IF statement as calculated column and use this as filter

[AREO-07]

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 Date	Loyalty Card Number	Liters	Result
Nov-19	AC528974	20
Apr-19	AC528974	9
May-19	AC528974	33
Dec-19	AA062113	10
May-19	AA679780	9
Nov-19	AB333164	10	TRUE
Sep-19	AB333164	2	TRUE
May-19	AB333164	2	TRUE
Jun-20	AB333164	2	TRUE
Jun-20	AB333164	2	TRUE
Dec-19	AB333164	3	TRUE
Apr-19	AC813202	9
Mar-20	AB570381	5	TRUE
Mar-20	AB570381	3	TRUE
May-20	AB333164	22
Jul-20	AB333164	20
Jul-20	AC627150	10
Aug-20	AB333164	3
Aug-20	AB333164	4	TRUE
Aug-20	AB333164	8	TRUE
Aug-20	AB333164	5	TRUE
Sep-20	AB333164	5	TRUE
Sep-20	AB333164	7	TRUE
Jul-20	AB333164	7	TRUE
Jul-20	AB570381	27	TRUE
Aug-20	AB570381	14	TRUE

 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.

[wdx223_Daniel]

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

[AlB]

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 

[AREO-07]

@AlB 

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

Hoping for your response.

Thanks

[AREO-07]

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.

[Fowmy]

@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

[AREO-07]

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