Creating a flag

[Anonymous]

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,

[v-alq-msft · Accepted solution]

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.

[Greg_Deckler]

@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]

@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]

@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.

[Anonymous]

@Greg_Deckler  i did something like this

Measure 3 =

Var Month =SELECTEDVALUE(Tabela[Month])

Var Year = SELECTEDVALUE(Tabela[Year])

Return

IF(MONTH(MAX(PEDIDO_CARTAO[OrderDate]))=Month &&YEAR(MAX(PEDIDO_CARTAO[OrderDate]))=Year ;"Y";"N")


And that works kinda fine, but need something else to be perfect.

since i selected August 2020, and this Client A have a order(order is the credit thing, this table will always show if someone have credit) on this date(see the last line), everything on the past must be "Y" too, the N will be only if the Customerdidnt appear on the selected month(i have another table "Customer" where everyone appears,example, for this Client A if there is no order, the last line should be that 23/07/2020 and "N" all the way up).

Thanks

[Greg_Deckler]

@Anonymous - This is really confusing to follow. Sample data, expected output, how is your visual configured? You say you are choosing August 2020, is this in a slicer? And your measure returns correctly but not for historical items. How are the historical items being displayed if you have filtered to August 2020? I'm confused. Can you share a PBIX. Can you share sample data (as text) that demonstrates the issue? Walk us through it because I'm not following what is happening on your end at all.

[amitchandak]

@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]

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

[TomMartens]

Hey @Anonymous ,

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

Regards,

Tom

[Anonymous]

Hey @TomMartens ,
Here is the link

PowerBi Sample 

Thanks

[Anonymous]

Hi Postigo

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