Forum Discussion

AREO-07's avatar
AREO-07
Regular Visitor
5 years ago

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

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      AREO-07
      Regular Visitor
      5 years ago

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

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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 

    • AREO-07's avatar
      AREO-07
      Regular Visitor
      5 years ago

      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's avatar
      AREO-07
      Regular Visitor
      5 years ago

      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

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Icon for Community Champion rankCommunity Champion
    5 years ago

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