topic Re: using IF statement as calculated column and use this as filter in DAX Commands and Tips

using IF statement as calculated column and use this as filter

AREO-07 — Mon, 02 Nov 2020 13:02:48 GMT

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.

Re: using IF statement as calculated column and use this as filter

Fowmy — Mon, 02 Nov 2020 13:56:09 GMT

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

Re: using IF statement as calculated column and use this as filter

AlB — Tue, 03 Nov 2020 09:53:50 GMT

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

Re: using IF statement as calculated column and use this as filter

wdx223_Daniel — Tue, 03 Nov 2020 00:42:00 GMT

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

Re: using IF statement as calculated column and use this as filter

AREO-07 — Tue, 03 Nov 2020 08:59:56 GMT

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

Re: using IF statement as calculated column and use this as filter

AREO-07 — Tue, 03 Nov 2020 09:07:17 GMT

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.

Re: using IF statement as calculated column and use this as filter

AREO-07 — Tue, 10 Nov 2020 07:41:25 GMT

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