Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Creating a flag

Hi everyone,

I have a database here show me all the shop information about my clients and i need to do a flag in power bi that show me if the client in some point of the life put some credit in the card they have.

For example, here i have the CLIENT A with this informations, when i choose on the slicer "July 2020" the flag should be 1 for EVERY LINE of my client(it doesnt care if the client was flag 0 in May/2020 or whatever, since he have a information in july 2020 it will be 1 in May/2020).


Here i put a example for flag 0,  i chose the same period "July 2020", but since client B doesnt exist in July, the flag will be 0

What im trying to do is, Show all my database(Duplicate names will appear and its ok) and create this flag, where i always will use the max date of OrderDate to relate with the date i put in my slicer(in resume, put those printscreens on power BI).

Thanks,

Need Help
Show and Tell
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    Hi, Anonymous 

     

    You may create a calculated table and a measure as below.

    Calculated table:

    Calendar = CALENDARAUTO()

     

    Measure:

    Result = 
Var _month = SELECTEDVALUE('Calendar'[Date].[MonthNo])
Var _year = SELECTEDVALUE('Calendar'[Date].[Ano])

Return
IF(
    COUNTROWS(
        FILTER(
            ALL('relatorio_cartoes (74) - Copia'),
            YEAR('relatorio_cartoes (74) - Copia'[Order])=_year&&
            MONTH('relatorio_cartoes (74) - Copia'[Order])=_month&&
            'relatorio_cartoes (74) - Copia'[User]=SELECTEDVALUE('relatorio_cartoes (74) - Copia'[User])
        )
    )>0,
    "Y","N"
)

     

    Then you may use the 'Date' column from 'Calendar' to filter the result.

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

11 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    6 years ago

    Hey Anonymous ,

     

    please a pbix that contains sample data, upload the pbix to onedrive or dropbox and share the link.

     

    Regards,

    Tom

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi Postigo

        May I know the file you share at one drive , am I able to open using my free desktop Power BI ?

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous , if you are trying to create a column with help from slicer that is not possible.

    Can you share sample data and sample output in table format?

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      amitchandakthanks for your reply, but its not possible to create even a measure ?

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    Anonymous - So something along these lines should work:

     

    FLAG =
  VAR __Client = [Email]
  VAR __Table = FILTER('Table',[Client]=__Client && [Credit] = "Yes")
RETURN
  IF(ISBLANK(__Table),0,1)

     

    Not entirely sure how you are identifying a credit purchase. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Greg_DecklerThanks for the reply, well i just put the Credit column as a help to see if the measure was right. I need to do this column/measure.

      Edit: sorry i understand that i left the collumn, the "credit collumn" is just if the costumer did or didnt a order on the selected month of slicer, if yes i should change all the rows of that client(it doesnt care the month,all the historic) to yes, and no if no

      Thanks

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        6 years ago

        Anonymous - Right, that was the intention of the calculation. It is intended as a column. Basically what it says is, get the current customer in this row and save it in the variable __Email. Now, filter the entire table and return only the rows where the customer matches __Email AND where the credit column equals "Yes". If this table (__Table) has no rows, then return 0, otherwise, return 1.

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    6 years ago

    Hi, Anonymous 

     

    You may create a calculated table and a measure as below.

    Calculated table:

    Calendar = CALENDARAUTO()

     

    Measure:

    Result = 
Var _month = SELECTEDVALUE('Calendar'[Date].[MonthNo])
Var _year = SELECTEDVALUE('Calendar'[Date].[Ano])

Return
IF(
    COUNTROWS(
        FILTER(
            ALL('relatorio_cartoes (74) - Copia'),
            YEAR('relatorio_cartoes (74) - Copia'[Order])=_year&&
            MONTH('relatorio_cartoes (74) - Copia'[Order])=_month&&
            'relatorio_cartoes (74) - Copia'[User]=SELECTEDVALUE('relatorio_cartoes (74) - Copia'[User])
        )
    )>0,
    "Y","N"
)

     

    Then you may use the 'Date' column from 'Calendar' to filter the result.

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.